MySQL meldet multiple Fehler. Bitte schreibe folgende SQL-Anweisung komplett neu: SET…
- User
MySQL meldet multiple Fehler. Bitte schreibe folgende SQL-Anweisung komplett neu:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00";
CREATE TABLE
back_order_list(idint(30) NOT NULL,receiving_idint(30) NOT NULL,po_idint(30) NOT NULL,bo_codevarchar(50) NOT NULL,supplier_idint(30) NOT NULL,amountfloat NOT NULL,discount_percfloat NOT NULL DEFAULT 0,discountfloat NOT NULL DEFAULT 0,tax_percfloat NOT NULL DEFAULT 0,taxfloat NOT NULL DEFAULT 0,remarkstext DEFAULT NULL,statustinyint(4) NOT NULL DEFAULT 0 COMMENT '0 = pending, 1 = partially received, 2 =received',date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
back_order_list(id,receiving_id,po_id,bo_code,supplier_id,amount,discount_perc,discount,tax_perc,tax,remarks,status,date_created,date_updated) VALUES (1, 1, 1, 'BO-0001', 1, 40740, 3, 1125, 12, 4365, NULL, 1, '2021-11-03 11:20:38', '2021-11-03 11:20:51'), (2, 2, 1, 'BO-0002', 1, 20370, 3, 562.5, 12, 2182.5, NULL, 2, '2021-11-03 11:20:51', '2021-11-03 11:21:00'), (3, 4, 2, 'BO-0003', 2, 42826, 5, 2012.5, 12, 4588.5, NULL, 1, '2021-11-03 11:51:41', '2021-11-03 11:52:02'), (4, 5, 2, 'BO-0004', 2, 10640, 5, 500, 12, 1140, NULL, 2, '2021-11-03 11:52:02', '2021-11-03 11:52:15');CREATE TABLE
bo_items(bo_idint(30) NOT NULL,item_idint(30) NOT NULL,quantityint(30) NOT NULL,pricefloat NOT NULL DEFAULT 0,unitvarchar(50) NOT NULL,totalfloat NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
bo_items(bo_id,item_id,quantity,price,unit,total) VALUES (1, 1, 250, 150, 'pcs', 37500), (2, 1, 125, 150, 'pcs', 18750), (3, 2, 150, 200, 'Boxes', 30000), (3, 4, 50, 205, 'pcs', 10250), (4, 2, 50, 200, 'Boxes', 10000);CREATE TABLE
item_list(idint(30) NOT NULL,nametext NOT NULL,descriptiontext NOT NULL,supplier_idint(30) NOT NULL,costfloat NOT NULL DEFAULT 0,statustinyint(1) NOT NULL DEFAULT 1,date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
item_list(id,name,description,supplier_id,cost,status,date_created,date_updated) VALUES (1, 'Item 101', 'Sample Only', 1, 150, 1, '2021-11-02 10:01:55', '2021-11-02 10:01:55'), (2, 'Item 102', 'Sample only', 2, 200, 1, '2021-11-02 10:02:12', '2021-11-02 10:02:12'), (3, 'Item 103', 'Sample', 1, 185, 1, '2021-11-02 10:02:27', '2021-11-02 10:02:27'), (4, 'Item 104', 'Sample only', 2, 205, 1, '2021-11-02 10:02:47', '2021-11-02 10:02:47');CREATE TABLE
po_items(po_idint(30) NOT NULL,item_idint(30) NOT NULL,quantityint(30) NOT NULL,pricefloat NOT NULL DEFAULT 0,unitvarchar(50) NOT NULL,totalfloat NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
po_items(po_id,item_id,quantity,price,unit,total) VALUES (1, 1, 500, 150, 'pcs', 75000), (2, 2, 300, 200, 'Boxes', 60000), (2, 4, 200, 205, 'pcs', 41000);CREATE TABLE
purchase_order_list(idint(30) NOT NULL,po_codevarchar(50) NOT NULL,supplier_idint(30) NOT NULL,amountfloat NOT NULL,discount_percfloat NOT NULL DEFAULT 0,discountfloat NOT NULL DEFAULT 0,tax_percfloat NOT NULL DEFAULT 0,taxfloat NOT NULL DEFAULT 0,remarkstext NOT NULL,statustinyint(4) NOT NULL DEFAULT 0 COMMENT '0 = pending, 1 = partially received, 2 =received',date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
purchase_order_list(id,po_code,supplier_id,amount,discount_perc,discount,tax_perc,tax,remarks,status,date_created,date_updated) VALUES (1, 'PO-0001', 1, 81480, 3, 2250, 12, 8730, 'Sample', 2, '2021-11-03 11:20:22', '2021-11-03 11:21:00'), (2, 'PO-0002', 2, 107464, 5, 5050, 12, 11514, 'Sample PO Only', 2, '2021-11-03 11:50:50', '2021-11-03 11:52:15');CREATE TABLE
receiving_list(idint(30) NOT NULL,form_idint(30) NOT NULL,from_ordertinyint(1) NOT NULL DEFAULT 1 COMMENT '1=PO ,2 = BO',amountfloat NOT NULL DEFAULT 0,discount_percfloat NOT NULL DEFAULT 0,discountfloat NOT NULL DEFAULT 0,tax_percfloat NOT NULL DEFAULT 0,taxfloat NOT NULL DEFAULT 0,stock_idstext DEFAULT NULL,remarkstext DEFAULT NULL,date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
receiving_list(id,form_id,from_order,amount,discount_perc,discount,tax_perc,tax,stock_ids,remarks,date_created,date_updated) VALUES (1, 1, 1, 40740, 3, 1125, 12, 4365, '1', 'Sample', '2021-11-03 11:20:38', '2021-11-03 11:20:38'), (2, 1, 2, 20370, 3, 562.5, 12, 2182.5, '2', '', '2021-11-03 11:20:51', '2021-11-03 11:20:51'), (3, 2, 2, 20370, 3, 562.5, 12, 2182.5, '3', 'Success', '2021-11-03 11:21:00', '2021-11-03 11:21:00'), (4, 2, 1, 64638, 5, 3037.5, 12, 6925.5, '4,5', 'Sample Receiving (Partial)', '2021-11-03 11:51:41', '2021-11-03 11:51:41'), (5, 3, 2, 32186, 5, 1512.5, 12, 3448.5, '6,7', 'BO Receive (Partial)', '2021-11-03 11:52:02', '2021-11-03 11:52:02'), (6, 4, 2, 10640, 5, 500, 12, 1140, '8', 'Sample Success', '2021-11-03 11:52:15', '2021-11-03 11:52:15');CREATE TABLE
return_list(idint(30) NOT NULL,return_codevarchar(50) NOT NULL,supplier_idint(30) NOT NULL,amountfloat NOT NULL DEFAULT 0,remarkstext DEFAULT NULL,stock_idstext NOT NULL,date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
return_list(id,return_code,supplier_id,amount,remarks,stock_ids,date_created,date_updated) VALUES (1, 'R-0001', 2, 3025, 'Sample Issue', '16,17', '2021-11-03 13:45:53', '2021-11-03 13:45:53');CREATE TABLE
sales_list(idint(30) NOT NULL,sales_codevarchar(50) NOT NULL,clienttext DEFAULT NULL,amountfloat NOT NULL DEFAULT 0,remarkstext DEFAULT NULL,stock_idstext NOT NULL,date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
sales_list(id,sales_code,client,amount,remarks,stock_ids,date_created,date_updated) VALUES (1, 'SALE-0001', 'John Smith', 7625, 'Sample Remarks', '24,25,26', '2021-11-03 14:03:30', '2021-11-03 14:08:27');CREATE TABLE
stock_list(idint(30) NOT NULL,item_idint(30) NOT NULL,quantityint(30) NOT NULL,unitvarchar(250) DEFAULT NULL,pricefloat NOT NULL DEFAULT 0,totalfloat NOT NULL DEFAULT current_timestamp(),typetinyint(1) NOT NULL DEFAULT 1 COMMENT '1=IN , 2=OUT',date_createddatetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
stock_list(id,item_id,quantity,unit,price,total,type,date_created) VALUES (1, 1, 250, 'pcs', 150, 37500, 1, '2021-11-03 11:20:38'), (2, 1, 125, 'pcs', 150, 18750, 1, '2021-11-03 11:20:51'), (3, 1, 125, 'pcs', 150, 18750, 1, '2021-11-03 11:21:00'), (4, 2, 150, 'Boxes', 200, 30000, 1, '2021-11-03 11:51:41'), (5, 4, 150, 'pcs', 205, 30750, 1, '2021-11-03 11:51:41'), (6, 2, 100, 'Boxes', 200, 20000, 1, '2021-11-03 11:52:02'), (7, 4, 50, 'pcs', 205, 10250, 1, '2021-11-03 11:52:02'), (8, 2, 50, 'Boxes', 200, 10000, 1, '2021-11-03 11:52:15'), (16, 2, 10, 'pcs', 200, 2000, 2, '2021-11-03 13:45:53'), (17, 4, 5, 'boxes', 205, 1025, 2, '2021-11-03 13:45:53'), (24, 1, 10, 'pcs', 150, 1500, 2, '2021-11-03 14:08:27'), (25, 2, 5, 'pcs', 200, 1000, 2, '2021-11-03 14:08:27'), (26, 4, 25, 'boxes', 205, 5125, 2, '2021-11-03 14:08:27');CREATE TABLE
supplier_list(idint(30) NOT NULL,nametext NOT NULL,addresstext NOT NULL,cpersontext NOT NULL,contacttext NOT NULL,statustinyint(1) NOT NULL DEFAULT 1,date_createddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
supplier_list(id,name,address,cperson,contact,status,date_created,date_updated) VALUES (1, 'Supplier 101', 'Sample Supplier Address 101', 'Supplier Staff 101', '09123456789', 1, '2021-11-02 09:36:19', '2021-11-02 09:36:19'), (2, 'Supplier 102', 'Sample Address 102', 'Supplier Staff 102', '0987654332', 1, '2021-11-02 09:36:54', '2021-11-02 09:36:54');CREATE TABLE
system_info(idint(30) NOT NULL,meta_fieldtext NOT NULL,meta_valuetext NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
system_info(id,meta_field,meta_value) VALUES (1, 'name', 'Stock Management System - PHP'), (6, 'short_name', 'SMS- PHP'), (11, 'logo', 'uploads/logo-1635816671.png'), (13, 'user_avatar', 'uploads/user_avatar.jpg'), (14, 'cover', 'uploads/cover-1635816671.png'), (15, 'content', 'Array');CREATE TABLE
users(idint(50) NOT NULL,firstnamevarchar(250) NOT NULL,middlenametext DEFAULT NULL,lastnamevarchar(250) NOT NULL,usernametext NOT NULL,passwordtext NOT NULL,avatartext DEFAULT NULL,last_logindatetime DEFAULT NULL,typetinyint(1) NOT NULL DEFAULT 0,date_addeddatetime NOT NULL DEFAULT current_timestamp(),date_updateddatetime DEFAULT NULL ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO
users(id,firstname,middlename,lastname,username,password,avatar,last_login,type,date_added,date_updated) VALUES (1, 'Adminstrator', NULL, 'Admin', 'admin', '0192023a7bbd73250516f069df18b500', 'uploads/avatar-1.png?v=1635556826', NULL, 1, '2021-01-20 14:02:37', '2021-10-30 09:20:26'), (10, 'John', NULL, 'Smith', 'jsmith', '39ce7e2a8573b41ce73b5ba41617f8f7', 'uploads/avatar-10.png?v=1635920488', NULL, 2, '2021-11-03 14:21:28', '2021-11-03 14:21:28'), (11, 'Claire', NULL, 'Blake', 'cblake', 'cd74fae0a3adf459f73bbf187607ccea', 'uploads/avatar-11.png?v=1635920566', NULL, 1, '2021-11-03 14:22:46', '2021-11-03 14:22:46');CREATE TABLE
user_meta(user_idint(30) NOT NULL,meta_fieldtext NOT NULL,meta_valuetext NOT NULL,date_createddatetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;ALTER TABLE
back_order_listADD PRIMARY KEY (id), ADD KEYsupplier_id(supplier_id), ADD KEYpo_id(po_id), ADD KEYreceiving_id(receiving_id);ALTER TABLE
bo_itemsADD KEYitem_id(item_id), ADD KEYbo_id(bo_id);ALTER TABLE
item_listADD PRIMARY KEY (id), ADD KEYsupplier_id(supplier_id);ALTER TABLE
po_itemsADD KEYpo_id(po_id), ADD KEYitem_id(item_id);ALTER TABLE
purchase_order_listADD PRIMARY KEY (id), ADD KEYsupplier_id(supplier_id);ALTER TABLE
receiving_listADD PRIMARY KEY (id);ALTER TABLE
return_listADD PRIMARY KEY (id), ADD KEYsupplier_id(supplier_id);ALTER TABLE
sales_listADD PRIMARY KEY (id);ALTER TABLE
stock_listADD PRIMARY KEY (id), ADD KEYitem_id(item_id);ALTER TABLE
supplier_listADD PRIMARY KEY (id);ALTER TABLE
system_infoADD PRIMARY KEY (id);ALTER TABLE
usersADD PRIMARY KEY (id);ALTER TABLE
user_metaADD KEYuser_id(user_id);ALTER TABLE
back_order_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;ALTER TABLE
item_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;ALTER TABLE
purchase_order_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;ALTER TABLE
receiving_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;ALTER TABLE
return_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;ALTER TABLE
sales_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;ALTER TABLE
stock_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=27;ALTER TABLE
supplier_listMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;ALTER TABLE
system_infoMODIFYidint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;ALTER TABLE
usersMODIFYidint(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;ALTER TABLE
back_order_listADD CONSTRAINTback_order_list_ibfk_1FOREIGN KEY (supplier_id) REFERENCESsupplier_list(id) ON DELETE CASCADE, ADD CONSTRAINTback_order_list_ibfk_2FOREIGN KEY (po_id) REFERENCESpurchase_order_list(id) ON DELETE CASCADE, ADD CONSTRAINTback_order_list_ibfk_3FOREIGN KEY (receiving_id) REFERENCESreceiving_list(id) ON DELETE CASCADE;ALTER TABLE
bo_itemsADD CONSTRAINTbo_items_ibfk_1FOREIGN KEY (item_id) REFERENCESitem_list(id) ON DELETE CASCADE, ADD CONSTRAINTbo_items_ibfk_2FOREIGN KEY (bo_id) REFERENCESback_order_list(id) ON DELETE CASCADE;ALTER TABLE
item_listADD CONSTRAINTitem_list_ibfk_1FOREIGN KEY (supplier_id) REFERENCESsupplier_list(id) ON DELETE CASCADE;ALTER TABLE
po_itemsADD CONSTRAINTpo_items_ibfk_1FOREIGN KEY (po_id) REFERENCESpurchase_order_list(id) ON DELETE CASCADE, ADD CONSTRAINTpo_items_ibfk_2FOREIGN KEY (item_id) REFERENCESitem_list(id) ON DELETE CASCADE;ALTER TABLE
purchase_order_listADD CONSTRAINTpurchase_order_list_ibfk_1FOREIGN KEY (supplier_id) REFERENCESsupplier_list(id) ON DELETE CASCADE;ALTER TABLE
return_listADD CONSTRAINTreturn_list_ibfk_1FOREIGN KEY (supplier_id) REFERENCESsupplier_list(id) ON DELETE CASCADE;ALTER TABLE
stock_listADD CONSTRAINTstock_list_ibfk_1FOREIGN KEY (item_id) REFERENCESitem_list(id) ON DELETE CASCADE; COMMIT; - Assistant
Um die SQL-Anweisungen zu überarbeiten und sicherzustellen, dass die Wahrscheinlichkeit von Fehlern verringert wird, können wir einige Anpassungen in Bezug auf die Datenbankstruktur und die Einschränkungen vornehmen. Im Folgenden findest du eine überarbeitete Version:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; -- Tabelle back_order_list neu erstellen CREATE TABLE `back_order_list` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `receiving_id` INT(11) NOT NULL, `po_id` INT(11) NOT NULL, `bo_code` VARCHAR(50) NOT NULL, `supplier_id` INT(11) NOT NULL, `amount` FLOAT NOT NULL, `discount_perc` FLOAT NOT NULL DEFAULT 0, `discount` FLOAT NOT NULL DEFAULT 0, `tax_perc` FLOAT NOT NULL DEFAULT 0, `tax` FLOAT NOT NULL DEFAULT 0, `remarks` TEXT DEFAULT NULL, `status` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '0 = pending, 1 = partially received, 2 = received', `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `supplier_id` (`supplier_id`), KEY `po_id` (`po_id`), KEY `receiving_id` (`receiving_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Restliche Tabellen analog neu erstellen, hier ein Beispiel für bo_items CREATE TABLE `bo_items` ( `bo_id` INT(11) NOT NULL, `item_id` INT(11) NOT NULL, `quantity` INT(11) NOT NULL, `price` FLOAT NOT NULL DEFAULT 0, `unit` VARCHAR(50) NOT NULL, `total` FLOAT NOT NULL DEFAULT 0, KEY `item_id` (`item_id`), KEY `bo_id` (`bo_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Restliche Tabellen wie stock_list, supplier_list, etc. -- Längen bei INT auf 11 setzen, um Standard zu folgen -- AUTO_INCREMENT für IDs definieren, wo notwendig -- Daten einfügen INSERT INTO `back_order_list` (`receiving_id`, `po_id`, `bo_code`, `supplier_id`, `amount`, `discount_perc`, `discount`, `tax_perc`, `tax`, `remarks`, `status`, `date_created`, `date_updated`) VALUES (1, 1, 'BO-0001', 1, 40740, 3, 1125, 12, 4365, NULL, 1, '2021-11-03 11:20:38', '2021-11-03 11:20:51'); -- Weitere Einfügungen analog -- Foreign Key constraints ALTER TABLE `back_order_list` ADD CONSTRAINT `back_order_list_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `supplier_list` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `back_order_list_ibfk_2` FOREIGN KEY (`po_id`) REFERENCES `purchase_order_list` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `back_order_list_ibfk_3` FOREIGN KEY (`receiving_id`) REFERENCES `receiving_list` (`id`) ON DELETE CASCADE; -- Weitere Foreign Key constraints für andere Tabellen COMMIT;Änderungen:
- Anpassung der
INT-Größe auf 11, was standardmäßig üblich ist. - Konsistente Verwendung von
AUTO_INCREMENTfür Primärschlüssel. - Vereinfachung und Korrektur der Fremdschlüsseleinschränkungen.
- Nutzung von Standardwerten für Timestamps und Anmerkungstypen.
Bitte prüfe die spezifischen Änderungen für deine Datenbank. Es ist wichtig, die Datenschlüsselziele richtig zu definieren und sicherzustellen, dass die Fremdschlüsseleinschränkungen korrekt eingerichtet sind, um zukünftige Fehler zu vermeiden.
- Anpassung der
- User
Wie müsste "SET time_zone = “+00:00”;" für Deutschland aussehen?
- Assistant
Um die Zeitzone für Deutschland einzustellen, könntest du die Zeitzone auf "Europe/Berlin" setzen, da Deutschland in der mitteleuropäischen Zeitzone (CET) liegt, die im Sommer zur mitteleuropäischen Sommerzeit (CEST) wechselt. Hier ist, wie die Anweisung in MySQL aussehen würde:
SET time_zone = 'Europe/Berlin';Es ist wichtig sicherzustellen, dass der MySQL-Server korrekt konfiguriert ist und die Zeitzonendaten geladen sind, um solche identifizierten Zeitzonen zu unterstützen. Falls du stattdessen die Zeitzone manuell einstellen möchtest, kannst du auch den Offset verwenden. CET ist UTC+1 und CEST ist UTC+2, womit die folgende Einstellung für die normale Zeit entsprechen würde:
SET time_zone = '+01:00';Diese Einstellung würde standardmäßig auf die mitteleuropäische Zeit verweisen. Beachte, dass die manuelle Einstellung auf einen Offset wie diesem keine automatische Anpassung für die Sommerzeit bietet.