[Progress Communities] [Progress OpenEdge ABL] Forum Post: How to add a FOREIGN KEY to an existing table?

[YerAmil]

Hello, I am trying to add a foreign key to my existing table using by SQL command. ALTER TABLE PUB."Order" ADD CONSTRAINT ORDER_CUSTOMER_FK FOREIGN KEY ("Cust_Num") REFERENCES PUB."Customer"("Cust_Num"); I do it in the Dbeaver. But I got the following error: SQL Error [HY000]: [DataDirect][OpenEdge JDBC Driver][OpenEdge] No matching key defined for the referenced table (7545) The ProKb says about this error that: Cause Columns defining a primary or foreign key must not accept the NULL value. Resolution Make sure that the table columns chosen as primary or foreign key do not accept NULL values by making them mandatory. In a .df file a line mentioning: MANDATORY should be visible for those columns. These are the data definitions for my two test tables. I wanna to create a foreign key between Order.Cust_Num and Customer.Cust_Num fields. Both fields have the MANDATORY option: ADD TABLE "Customer" AREA "Customer" DUMP-NAME "customer" ADD FIELD "Cust_Num" OF "Customer" AS character FORMAT "x(8)" INITIAL "" POSITION 2 MAX-WIDTH 20 ORDER 10 MANDATORY ADD FIELD "Name" OF "Customer" AS character FORMAT "x(20)" INITIAL "" POSITION 3 MAX-WIDTH 20 ORDER 20 ADD INDEX "CustNumIdx" ON "Customer" AREA "Customer" UNIQUE PRIMARY INDEX-FIELD "Cust_Num" ASCENDING ADD TABLE "Order" AREA "Order" DUMP-NAME "order" ADD FIELD "Order_Num" OF "Order" AS character FORMAT "x(8)" INITIAL "" POSITION 2 MAX-WIDTH 20 ORDER 10 MANDATORY ADD FIELD "Cust_Num" OF "Order" AS character FORMAT "x(8)" INITIAL "" POSITION 3 MAX-WIDTH 20 ORDER 20 MANDATORY ADD INDEX "OrderNumIdx" ON "Order" AREA "OrderIDX" UNIQUE PRIMARY INDEX-FIELD "Order_Num" ASCENDING ADD INDEX "CustNumIdx" ON "Order" AREA "Customer" INDEX-FIELD "Cust_Num" ASCENDING Please advise me what I'm doing wrong?

Continue reading...