Hello Everyone! I am working on a project for my class, I created the tables with no problem but for some reason my insert from three command and on errors out with ERROR 1452. I need some assistant. I am getting four of them: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails: two in line_item and one is in orders. Here is my codes: -- Drops the database if it already exists DROP DATABASE yourlastname_ACMEOnline; -- Creates the database CREATE DATABASE yourlastname_ACMEOnline; -- Opens the database USE yourlastname_ACMEOnline; -- CATEGORY table DROP table if exists category; CREATE TABLE category( -- ...... ShippingPerPound decimal(4,2), Category_Name varchar(35), OffersAllowed ENUM('y', 'n'), CONSTRAINT category_pk PRIMARY KEY(Category_Name) ); -- This table works Drop table if exists offer; CREATE TABLE offer( OfferCode varchar(15), DiscountAmt varchar(35) NOT NULL, MinAmount decimal (4,2) NOT NULL, ExpirationDate date NOT NULL, CONSTRAINT offer_pk PRIMARY KEY (OfferCode) ); -- ITEM table DROP table if exists item; CREATE TABLE item( -- .... Item_Number integer NOT NULL , Item_Name varchar(32) NOT NULL, Category_Name varchar(35) NOT NULL, Description varchar(255), Model varchar(50) NOT NULL, Price decimal(8,2) NOT NULL, CONSTRAINT item_Category_Name PRIMARY KEY (Item_Number), CONSTRAINT item_Category_Name_fk FOREIGN KEY (Category_Name) REFERENCES CATEGORY(Category_Name) ON UPDATE CASCADE ); -- this table works -- CUSTOMER table DROP table if exists customer; CREATE TABLE customer( CustomerID integer UNSIGNED AUTO_INCREMENT, -- .... CustomerName varchar(50) not null, Address varchar(150) not null, Email varchar(80), home_bus ENUM ('home', 'business'), CONSTRAINT customer_pk PRIMARY KEY(CustomerID) ); -- this table works but check for discriminator before handing me in. -- HOME table DROP table if exists home; CREATE TABLE home( -- .... CustomerID integer UNSIGNED, CreditCardNum char(16) not null, CardExpiration char(6) not null, PRIMARY KEY(CustomerID), CONSTRAINT home_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE CASCADE ); -- table works -- BUSINESS table DROP table if exists business; CREATE TABLE business( CustomerID integer UNSIGNED, -- ..... PaymentTerms varchar(50) not null, PRIMARY KEY(CustomerID), CONSTRAINT business_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE CASCADE ); -- table works -- CONTACT table DROP table if exists purchase_contact; CREATE TABLE purchase_contact( CustomerID integer UNSIGNED, ContactName varchar(50), ContactPhone char(12) NOT NULL, CONSTRAINT contact_pk PRIMARY KEY(CustomerID, ContactName), CONSTRAINT contact_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES BUSINESS(CustomerID) ON UPDATE CASCADE ); -- ORDERS table Drop table if exists orders; CREATE TABLE orders( OrderID integer UNSIGNED AUTO_INCREMENT, Total decimal (10, 2), CustomerID integer UNSIGNED, OfferCode varchar(15), CONSTRAINT order_OrderID_pk PRIMARY KEY(OrderID), CONSTRAINT order_CustomerID_fk FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE CASCADE, CONSTRAINT order_OfferCode_fk FOREIGN KEY(OfferCode) REFERENCES OFFER(OfferCode) ON UPDATE CASCADE ); -- LINE_ITEM table Drop table if exists line_item; CREATE TABLE line_item( OrderID integer UNSIGNED, Item_Number integer NOT NULL, Quantity varchar(255), Shipping_amount decimal(6,2), PRIMARY KEY (Item_Number, OrderID), CONSTRAINT home_item_fk FOREIGN KEY (Item_Number) REFERENCES item(Item_Number) ON UPDATE CASCADE, CONSTRAINT home_item2_fk FOREIGN KEY (OrderID) REFERENCES orders(OrderID) ON UPDATE CASCADE -- ..... ); -- GUARANTEE table Drop table if exists guarantee; CREATE TABLE guarantee( OrderID integer UNSIGNED, CustomerID integer UNSIGNED, OfferCode varchar(15), -- ..... URL varchar(50), RefundAmount decimal(12,2), CONSTRAINT guarantee_pk PRIMARY KEY (OrderID, CustomerID), CONSTRAINT guarantee_OrderID_fk FOREIGN KEY (OrderID) REFERENCES ORDERS(OrderID), CONSTRAINT guarantee_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES HOME(CustomerID) ); -- CUSTOMER data: Janine Jeffers START TRANSACTION; INSERT INTO customer VALUES(0, 'Janine Jeffers','152 Lomb Memorial Dr., Rochester, NY 14623','[email protected]', NULL); INSERT INTO home (CustomerID, CreditCardNum, CardExpiration) SELECT CustomerID, '1234567890123456', '012014' FROM customer WHERE CustomerName='Janine Jeffers'; UPDATE customer SET home_bus='home' WHERE CustomerName='Janine Jeffers'; INSERT INTO orders (Total, CustomerID, OfferCode) SELECT '4919.75',CustomerID,'4567890123' FROM customer WHERE CustomerName='Janine Jeffers'; INSERT INTO line_item VALUES(1,4,1, .99); INSERT INTO line_item VALUES(1,2, 2, 3.99); INSERT INTO line_item VALUES(1,3, 3, NULL); COMMIT; here is my files: https://drive.google.com/folderview?id=0Bz0XG2NsBlFwUVBNdk5HeEkyeEU&usp=sharing Thanks for your help.
I suggest you visit this website you will get the solution, I tried and it worked.
http://bit.ly/1bUVmuW
SOURCE: After installing DB2 query is not executing
could you check whether you are connected to the database, where you are trying to create table,
check the database list, then connect to the database you want then execute the table creation ddl,
if no db found then create a database then proceed.
Java JDBC Tutorial
Java JDBC Tutorial
Java application calls the JDBC library. JDBC loads a driver which talks to the database. We can change database engines without changing database code.169 views
Usually answered in minutes!
The databases was created and I can build the table with no problem but when I did my inserts I am having four errors with my constraints
Here is my .sql files.
https://drive.google.com/folderview?id=0...
×