CODEWITHSUNDEEP
mysqlsql
user962971
user962971

Reputation: 47

Cannot add or update a child row

I am trying to add records into two tables below,

CREATE TABLE customer
(Custno     CHAR(3),
 Custname   VARCHAR(25) NOT NULL,
 Custstreet VARCHAR(30) NOT NULL,
 Custcity   VARCHAR(15) NOT NULL,
 Custprov   VARCHAR(3) NOT NULL,
 Custpcode  VARCHAR(6) NOT NULL,
 Disc       DECIMAL(3,1),
 Balance    DECIMAL(7,2),
 Credlimit  DECIMAL(5),
 Srepno     CHAR(3),
 CONSTRAINT pkcustno PRIMARY KEY (Custno),
 CONSTRAINT fksrepno FOREIGN KEY (Srepno) REFERENCES salesrep(Srepno)
);


CREATE TABLE orders
(Orderno   CHAR(5) UNIQUE NOT NULL,
 Orderdate DATE,
 Custno    CHAR(3) NOT NULL,
 CONSTRAINT fkordercust FOREIGN KEY (Custno) REFERENCES customer (Custno)
);

When adding like this, 

INSERT INTO orders(Orderno, Orderdate, Custno) VALUES('14587','2011-11-09', '125' );
INSERT INTO orders(Orderno, Orderdate, Custno) VALUES('11547','2011-11-07', '125' );

I get, "Cannot add or update a child row: a foreign key constraint fails (sh.orders, CONSTRAINT fkordercust FOREIGN KEY (Custno) REFERENCES customer (Custno)) " Is something wrong the table?

Upvotes: 4

Views: 301

Answers (5)

Matthew Watson
Matthew Watson

Reputation: 14243

You have created a foreign key to the customer table, but ( apparently ) inserted no data into it.

Upvotes: 3

competent_tech
competent_tech

Reputation: 44931

Your table is fine, you just don't have a customer with a CustNo of '125' in the database.

Upvotes: 3

paxdiablo
paxdiablo

Reputation: 881553

Do you actually have a customer row for customer number 125? I think not. The error message is telling you exactly what's wrong.

The foreign key constraint which ensures that no orders can be created for non-existent customers is being violated:

CONSTRAINT fkordercust FOREIGN KEY (Custno) REFERENCES customer (Custno)

Create the customer row first then you can add order rows for that customer to your heart's content.

Upvotes: 3

Adrian Cornish
Adrian Cornish

Reputation: 23868

Do you have a customer with a number of 125?

Upvotes: 1

Adam Wenger
Adam Wenger

Reputation: 17540

You do not have a customer with CustNo = '125'. Because of this, the Foreign key fails. You are trying to place an order for a non-existent customer, the DB throws an error.

Upvotes: 7

Related Questions