Sql updating tables with foreign keys

Dear tom, please let me know how can i update a record in the primary key field which behaves as a foreign key to other tables without altering (disabling) the primary/foreign constraint????

sql updating tables with foreign keys-74sql updating tables with foreign keys-1sql updating tables with foreign keys-69sql updating tables with foreign keys-15

If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.select table_name, constraint_name, DEFERRABLE, DEFERRED, VALIDATED 2 from user_constraints 3 where table_name in ('EMP', 'DEPT') 4 ORDER BY 1 ; TABLE_NAME CONSTRAINT_NAME DEFERRABLE DEFERRED VALIDATED ------------------------------ ----------------------------- DEPT PK_DEPTNO NOT DEFERRABLE IMMEDIATE VALIDATED EMP PK_EMPNO NOT DEFERRABLE IMMEDIATE VALIDATED EMP FK_DEPT NOT DEFERRABLE IMMEDIATE VALIDATED EMP SYS_C001402 NOT DEFERRABLE IMMEDIATE VALIDATED I tried: SQL set constraints pk_dept, fk_dept deferred ; set constraints pk_dept, fk_dept deferred * ERROR at line 1: ORA-02447: cannot defer a constraint that is not deferrable Check the Doc.It says: Specify NOT DEFERRABLE to indicate that this constraint is checked at the end of each DML statement.The foreign key is the anchor on the many side of a one-to-many (1: M) relationship, much as the primary or candidate key is the anchor on the one side of this relationship.A foreign key is the linchpin that makes sure you don't enter invalid data into a table.This makes sure that that every row in the Product Vendor table has a unique combination of Product ID and Vendor ID. A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table.

168

Leave a Reply