M-Amr Moussa M-Amr Moussa - 9 days ago 6
SQL Question

SQL error 150 Foreign key

i wanna add a foreign key to my table and it gives me an error , the reference has the same data type , any ideas ?
here's my code

CREATE TABLE order_task
(
order_number numeric (20) NOT NULL ,
order_sending_date TIMESTAMP,
order_profile_code varchar (20) NOT NULL ,
order_weight decimal (20.10) NOT NULL ,
order_piston_number numeric (1) NOT NULL
);
ALTER TABLE order_task ADD CONSTRAINT ord_num_dt_pk PRIMARY KEY (order_number , order_sending_date);

CREATE TABLE pre_product
(
pre_product_number numeric (5) NOT NULL ,
pre_product_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
pre_product_lenght_count numeric (5) NOT NULL ,
pre_product_rod_count numeric (5) NOT NULL,
pre_product_rod_lenght float (20) NOT NULL,
pre_product_weight float (20) NOT NULL,
pre_product_piston_number numeric (1) NOT NULL ,
pre_product_profile_code varchar (20) NOT NULL ,
pre_product_shift_number numeric (1) NOT NULL,
pre_product_employee varchar (40) NOT NULL ,
pre_product_scrap float (20) NOT NULL,
pre_product_scrap_percentage float (3,3) NOT NULL ,
pre_product_status varchar (40) NOT NULL,
pre_product_order_number numeric (20) NOT NULL ,
pre_product_order_date TIMESTAMP
);
ALTER TABLE pre_product ADD CONSTRAINT pp_num_date_pk PRIMARY KEY (pre_product_number,pre_product_date);

ALTER TABLE pre_product ADD CONSTRAINT pp_on_pp_fk FOREIGN KEY (pre_product_order_number)
REFERENCES order_task (order_number);

ALTER TABLE pre_product ADD CONSTRAINT pp_on_ot_fk FOREIGN KEY (pre_product_order_date)
REFERENCES order_task (order_sending_date);


the error appears in the last line when i try to add a foreign key between pre_product_order_date and order_sending_date

Answer

The primary key which you have created on the table is a composite primary key on two columns order_number , order_sending_date. So, Foreign key should be created on both the columns together and not just part of it. As pre_product is the child table for order_task table.

ALTER TABLE pre_product ADD CONSTRAINT pp_on_pp_fk FOREIGN KEY (pre_product_order_number_date) REFERENCES order_task (order_number,order_sending_date);

Comments