Oracle create foreign key

Oracle foreign keys enforce referential integrity between tables — the child table can’t have a CLIENTID that doesn’t exist in the parent table. The ALTER TABLE ADD CONSTRAINT syntax is the standard way to add FKs after the tables already exist. The cleanup section shows how to remove the constraint without dropping the table.

Setup

• Create primary or parent table

create table CLIENT_TABLE(
    CLIENTID number(19) primary key
);

• Create secondary or child table

create table CLIENT_PREFERENCES_TABLE(
    CLIENTID number(19),
    PREFERENCE varchar(100)
);

• Add Foreign Key Link

Link secondary table with primary table using primary table unique column

ALTER TABLE CLIENT_PREFERENCES_TABLE ADD CONSTRAINT CLIENT_PREFERENCES_TABLE_FK1 FOREIGN KEY (CLIENTID) REFERENCES CLIENT_TABLE (CLIENTID);

Cleanup

• Remove Foreign Key Link

ALTER TABLE CLIENT_PREFERENCES_TABLE DROP CONSTRAINT CLIENT_PREFERENCES_TABLE_FK1;

• Drop tables

drop table CLIENT_PREFERENCES_TABLE;
drop table CLIENT_TABLE;