Newbie - Einfacher Shop als Übung (7 Tabellen mit jeweils maximal 4 Spalten)

Garzec · 7 Juni 2020

Hallo zusammen,
Ich komme aus der ORM Ecke und möchte mir jetzt mal Raw SQL für MariaDb aneignen. Dafür habe ich mir einen einfachen Shop ausgedacht, ohne Benutzerverwaltung. Die Erstellung der Datenbank habe ich als Script begonnen und das ist der aktuelle Stand

Code:

CREATE DATABASE `dotnet-shop`;

CREATE TABLE `dotnet-shop`.`category` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `title_UNIQUE` (`title`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`customer` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`vendor` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name_UNIQUE` (`name`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`product` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendor_id` INT(10) UNSIGNED NOT NULL,
    `title` VARCHAR(50) NOT NULL,
    `price` DECIMAL(10 , 2 ) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `vendor_product_title` (`vendor_id` , `title`),
    CONSTRAINT `vendor_id` FOREIGN KEY (`id`)
        REFERENCES `vendor` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`order` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `customer_id` FOREIGN KEY (`id`)
        REFERENCES `customer` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`category_product` (
    `product_id` INT(10) UNSIGNED NOT NULL,
    `category_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`product_id` , `category_id`),
    CONSTRAINT `category_id` FOREIGN KEY (`category_id`)
        REFERENCES `category` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `product_id` FOREIGN KEY (`product_id`)
        REFERENCES `product` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`order_product` (
    `order_id` INT(10) UNSIGNED NOT NULL,
    `product_id` INT(10) UNSIGNED NOT NULL,
    `amount` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`order_id` , `product_id`),
    CONSTRAINT `order_id` FOREIGN KEY (`order_id`)
        REFERENCES `order` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `product_id` FOREIGN KEY (`product_id`)
        REFERENCES `product` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

Ich glaube es gibt fast nichts besonderes zu sagen, außer

- Produkte hängen immer am Vendor
- Orders hängen immer am Customer
- In der Produkttabelle ist der Titel zwar nicht Unique, aber ein einzelner Vendor soll keine Möglichkeit haben 2 Produkte gleich zu benennen. Daher der Unique Index.

Wenn ich das Script ausführe, bekomme ich mehrere Fehler. Unter anderem bei

Apply changes to order_product

Dort erhalte ich folgenden Output

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `dotnet-sho' at line 6 SQL Statement: CREATE TABLE `dotnet-shop`.`order_product` ( `order_id` INT UNSIGNED NOT NULL, `product_id` INT UNSIGNED NOT NULL, `amount` INT UNSIGNED NOT NULL, PRIMARY KEY (`order_id`, `product_id`), INDEX `product_id_idx` (`product_id` ASC) VISIBLE, CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `dotnet-shop`.`order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `dotnet-shop`.`product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Zeile 6 wäre die Erstellung des Foreign Keys, ich kann dort leider keinen Fehler erkennen. Ebenso der Fehler

Error 1005: Can't create table `dotnet-shop`.`order_product` (errno: 121 "Duplicate key on write or update") SQL Statement: CREATE TABLE `dotnet-shop`.`order_product` ( `order_id` INT UNSIGNED NOT NULL, `product_id` INT UNSIGNED NOT NULL, `amount` INT UNSIGNED NOT NULL, PRIMARY KEY (`order_id`, `product_id`), INDEX `product_id_idx` (`product_id` ASC), CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `dotnet-shop`.`order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `dotnet-shop`.`product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Wäre jemand so nett und könnte mir beim Script helfen? Möglicherweise gibt es noch Dinge, die ich verbessern kann?

akretschmer · 7 Juni 2020

warum läßt Du die id in product und order gleichzeitig PK und FK sein? Du meinst da bestimmt vendor_id und customer_id ...

akretschmer · 7 Juni 2020

Error 1022 - Can't write; duplicate key in table

Garzec · 7 Juni 2020

@akretschmer entschuldige bitte, also laut Docs müsste ich Product dann zu

Code:

    CONSTRAINT `vendor_id` FOREIGN KEY (`vendor_id`)
        REFERENCES `vendor` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE

aktualisieren und Order zu

Code:

    CONSTRAINT `customer_id` FOREIGN KEY (`customer_id`)
        REFERENCES `customer` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE

? Da bekomme ich bisher leider noch den Fehler

Error Code: 1005. Can't create table `dotnet-shop`.`order_product` (errno: 121 "Duplicate key on write or update") 0,013 sec

Edit: Oh, ok, jeder FK muss innerhalb der DB einzigartig sein? Welche Konvention würde man dann verwenden? Bei Produkt hieße der FK dann

FK_product_vendor

?

akretschmer · 7 Juni 2020

ja, aber so würde es a) mehr Sinn machen und b) siehe #3 und c) warum MySQL?

Garzec · 7 Juni 2020

Oder, weil sich ein FK ja nicht zwingend auf den PK einer anderen Tabelle beziehen muss doch eher

fk_product_vendor_id

?

Garzec · 7 Juni 2020

akretschmer schrieb:
warum MySQL?

Ich persönlich mag Postgres am liebsten, zumindest kam ich damit immer bestens zurecht vom Code her und vom Setup. Aber mein neuer AG verwendet MariaDB und kein ORM, also wollte ich mir das schon einmal etwas beibringen

akretschmer · 7 Juni 2020

Nun, das kann man schnell testen:

Code:

test=# create table m(id int);
CREATE TABLE
test=*# create table s(mid int references m);
FEHLER:  in Tabelle »m«, auf die verwiesen wird, gibt es keinen Primärschlüssel
test=*# rollback;
ROLLBACK
test=# create table m(id int primary key);
CREATE TABLE
test=*# create table s(mid int references m);
CREATE TABLE
test=*#

akretschmer · 7 Juni 2020

Garzec schrieb:
Aber mein neuer AG verwendet MariaDB

Kündigungsgrund.

Code:

mysql> CREATE TABLE `dotnet-shop`.`order_product` (
    ->     `order_id` INT(10) UNSIGNED NOT NULL,
    ->     `product_id` INT(10) UNSIGNED NOT NULL,
    ->     `amount` INT(10) UNSIGNED NOT NULL,
    ->     PRIMARY KEY (`order_id` , `product_id`),
    ->     CONSTRAINT `order_id` FOREIGN KEY (`order_id`)
    ->         REFERENCES `order` (`id`)
    ->         ON DELETE CASCADE ON UPDATE CASCADE,
    ->     CONSTRAINT `product_id` FOREIGN KEY (`product_id`)
    ->         REFERENCES `product` (`id`)
    ->         ON DELETE CASCADE ON UPDATE CASCADE
    -> )  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
ERROR 1022 (23000): Can't write; duplicate key in table 'order_product'
mysql> CREATE TABLE `dotnet-shop`.`order_product` (     `order_id` INT(10) UNSIGNED NOT NULL,     `product_id` INT(10) UNSIGNED NOT NULL,     `amount` INT(10) UNSIGNED NOT NULL,     PRIMARY KEY (`order_id` , `product_id`),     CONSTRAINT `order_id_x` FOREIGN KEY (`order_id`)         REFERENCES `order` (`id`)         ON DELETE CASCADE ON UPDATE CASCADE,     CONSTRAINT `product_id_x` FOREIGN KEY (`product_id`)         REFERENCES `product` (`id`)         ON DELETE CASCADE ON UPDATE CASCADE )  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
Query OK, 0 rows affected (0.07 sec)

mysql>

Finde den Unterschied, beachte #3.

Garzec · 7 Juni 2020

@akretschmer würde es dir etwas ausmachen, nochmal drüber zu schauen?

Habe alles angepasst, das Script lief jetzt erfolgreich durch

Code:

CREATE DATABASE `dotnet-shop`;

CREATE TABLE `dotnet-shop`.`category` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `title_UNIQUE` (`title`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`customer` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`vendor` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name_UNIQUE` (`name`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`product` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendor_id` INT(10) UNSIGNED NOT NULL,
    `title` VARCHAR(50) NOT NULL,
    `price` DECIMAL(10 , 2 ) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `vendor_product_title_UNIQUE` (`vendor_id` , `title`),
    CONSTRAINT `fk_product_vendor_id` FOREIGN KEY (`vendor_id`)
        REFERENCES `vendor` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`order` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_order_customer_id` FOREIGN KEY (`customer_id`)
        REFERENCES `customer` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`category_product` (
    `product_id` INT(10) UNSIGNED NOT NULL,
    `category_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`product_id` , `category_id`),
    CONSTRAINT `fk_category_product_category_id` FOREIGN KEY (`category_id`)
        REFERENCES `category` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_category_product_product_id` FOREIGN KEY (`product_id`)
        REFERENCES `product` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

CREATE TABLE `dotnet-shop`.`order_product` (
    `order_id` INT(10) UNSIGNED NOT NULL,
    `product_id` INT(10) UNSIGNED NOT NULL,
    `amount` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`order_id` , `product_id`),
    CONSTRAINT `fk_order_product_order_id` FOREIGN KEY (`order_id`)
        REFERENCES `order` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_order_product_product_id` FOREIGN KEY (`product_id`)
        REFERENCES `product` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;

akretschmer · 7 Juni 2020

Garzec schrieb:
@akretschmer würde es dir etwas ausmachen, nochmal drüber zu schauen?

MySQL-Support? Niemals!

Garzec · 7 Juni 2020

Ok schade, aber vielen Dank für deine Hilfe!

Newbie - Einfacher Shop als Übung (7 Tabellen mit jeweils maximal 4 Spalten)

Garzec

Benutzer

akretschmer

Datenbank-Guru

akretschmer

Datenbank-Guru

Garzec

Benutzer

akretschmer

Datenbank-Guru

Garzec

Benutzer

Garzec

Benutzer

akretschmer

Datenbank-Guru

akretschmer

Datenbank-Guru

Garzec

Benutzer

akretschmer

Datenbank-Guru

Garzec

Benutzer

Ähnliche Themen