0

I have created tables in MySQL Workbench as shown below :

customer TABLE

CREATE TABLE IF NOT EXISTS customer (
    customer_id NUMERIC(5) PRIMARY KEY NOT NULL,
    cust_name VARCHAR(30),
    city VARCHAR(15),
    grade NUMERIC(5)
);

orders TABLE

CREATE TABLE IF NOT EXISTS orders (
    order_num NUMERIC(5) PRIMARY KEY NOT NULL,
    purch_AMT DECIMAL(8 , 2 ),
    ord_date DATE,
    salesman NUMERIC(5) NOT NULL,
    FOREIGN KEY (salesman)
        REFERENCES salesman (salesman_id),
    customer NUMERIC(5) NOT NULL,
    FOREIGN KEY (customer)
        REFERENCES customer (customer_id)
);

salesman TABLE

CREATE TABLE IF NOT EXISTS salesman (
    salesman_id NUMERIC(5) PRIMARY KEY NOT NULL,
    salesman_name VARCHAR(50),
    city VARCHAR(15),
    commission DECIMAL(5 , 2 )
);

when i insert date in orders table in this columns

order_num NUMERIC(5) PRIMARY KEY NOT NULL,
purch_AMT DECIMAL(8 , 2 ),
ord_date DATE,

i get this message

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails

Barmar
  • 741,623
  • 53
  • 500
  • 612
user9339240
  • 11
  • 5
  • You tried to insert either an order number or salesman that isn't in those tables. – Barmar Apr 07 '20 at 20:37
  • It's the `customer` and `salesman` columns that are foreign keys. If you don't specify values, they default to 0. I assume the `customer` and `salesman` tables don't have rows with primary key value 0. So the foreign key value of 0 can't reference an existing row. – Bill Karwin Apr 07 '20 at 20:40

0 Answers0