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

Garzec

Benutzer
Beiträge
7
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?
 
Werbung:
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 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

?
 
Zuletzt bearbeitet:
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=*#
 
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.
 
@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;
 
Werbung:
Zurück
Oben