• A foreign key is a constraint that enforces referential integrity between two tables by ensuring that values in the foreign key column(s) must exist in the referenced table's primary key or unique key column(s).
  
  
• The foreign key columns must reference either the primary key or a unique key columns of the referenced table. The data types of the foreign key and referenced columns must be compatible.
  
  
• Foreign key constraints prevent orphaned rows and maintain data consistency across related tables. When a foreign key is defined, Oracle automatically creates an index on the foreign key columns if one doesn't already exist.
  

This table is used in the examples below:

CREATE TABLE table_unique_1
(
    id NUMBER(1,0)
,   id1 NUMBER(1,0)
,   id2 NUMBER(1,0)
,   CONSTRAINT cp_table_unique_1_id PRIMARY KEY (id) -- PRIMARY KEY
,   CONSTRAINT cu_table_unique_1_id UNIQUE (id1, id2) -- UNIQUE
);

Foreign key constraints can be created during table creation with CREATE TABLE or added later using ALTER TABLE.

1. Creating Foreign Keys with CREATE TABLE
   When creating a table, you can define foreign key constraints as part of the table definition. This approach is useful when the table structure and relationships are known during the initial design phase.
   

   CREATE TABLE table_foreign_key_1
   (
       id NUMBER(1,0)
   ,   CONSTRAINT cr_table_foreign_key_1_id FOREIGN KEY (id) REFERENCES table_unique_1 (id) -- the name of the foreign key constraint is explicit: "CR_table_foreign_key_1_ID"
   );
   

   Important: The column (id) referenced by the foreign key must have either a PRIMARY KEY or a UNIQUE constraint. Oracle will return an error if you attempt to reference a column without these constraints.
   
   Note: If the constraint name is omitted, Oracle will generate a system name automatically. While functional, explicit naming improves maintainability and troubleshooting:
   

   CREATE TABLE table_foreign_key_2
   (
       id NUMBER(1,0)
   ,   FOREIGN KEY (id) REFERENCES table_unique_1 (id) -- the foreign key constraint name will be dynamically generated by Oracle
   );
   

   Composite Foreign Keys: When the foreign key consists of multiple columns, all columns must be specified in the same order as they appear in the referenced unique or primary key constraint:
   

   CREATE TABLE table_foreign_key_3
   (
       id NUMBER(1,0)
   ,   fk_id1 NUMBER(1,0)
   ,   fk_id2 NUMBER(1,0)
   ,   CONSTRAINT cr_table_foreign_key_3_id FOREIGN KEY (fk_id1, fk_id2) REFERENCES table_unique_1 (id1, id2)
   );
   

2. Adding Foreign Keys with ALTER TABLE
   Foreign key constraints can be added to existing tables using ALTER TABLE. This approach is useful when modifying existing database schemas or when foreign key relationships are identified after table creation.
   First, create the table without the foreign key constraint:
   

   CREATE TABLE table_foreign_key_4
   (
       id NUMBER(1,0)
   );
   

   Then add the foreign key constraint using ALTER TABLE with an explicit constraint name:
   

   ALTER TABLE table_foreign_key_4 ADD CONSTRAINT cr_table_foreign_key_4_id FOREIGN KEY (id) REFERENCES table_unique_1 (id); -- the foreign key constraint name is explicit: "CR_TABLE_FOREIGN_KEY_4_ID"
   

   Alternatively, you can let Oracle generate the constraint name automatically:
   

   ALTER TABLE table_foreign_key_4 ADD FOREIGN KEY (id) REFERENCES table_unique_1 (id); -- the foreign key constraint name will be dynamically generated by Oracle
   

   Note: When adding a foreign key to a table with existing data, Oracle validates that all existing foreign key values have corresponding entries in the referenced table. If any orphaned rows exist, the ALTER TABLE statement will fail.
   

The ON DELETE CASCADE option automatically deletes child rows when the parent row is deleted. This maintains referential integrity by preventing orphaned rows, but use it carefully as data deletion cascades through the relationship chain.

CREATE TABLE table_foreign_key_5
(
    id NUMBER(1,0)
,   CONSTRAINT cr_table_foreign_key_5_id FOREIGN KEY (id) REFERENCES table_unique_1 (id) ON DELETE CASCADE
);

The "ON DELETE CASCADE" option means that if a row is deleted from the "table_unique_1" table, then any rows in the "table_foreign_key_5" table that reference it will also be deleted automatically.
Verify the creation of the constraint by querying the data dictionary:

SELECT constraint_name, constraint_type, generated, delete_rule
FROM user_constraints
WHERE table_name = 'TABLE_FOREIGN_KEY_5';

Query result:

+---------------------------+-----------------+----------------+-------------+
| CONSTRAINT_NAME           | CONSTRAINT_TYPE | GENERATED      | DELETE_RULE |
+---------------------------+-----------------+----------------+-------------+
| CR_TABLE_FOREIGN_KEY_5_ID | R               | GENERATED NAME | CASCADE     |
+---------------------------+-----------------+----------------+-------------+

Demonstration:
Insert test data into both tables:

INSERT INTO table_unique_1 (id, id1, id2) VALUES (0,0,0);
INSERT INTO table_unique_1 (id, id1, id2) VALUES (1,0,1);
INSERT INTO table_unique_1 (id, id1, id2) VALUES (2,1,0);

INSERT INTO table_foreign_key_5 (id) VALUES (0);
INSERT INTO table_foreign_key_5 (id) VALUES (1);
INSERT INTO table_foreign_key_5 (id) VALUES (2);

Delete parent rows to trigger the cascade:

DELETE FROM table_unique_1 WHERE id < 2;

Check the data in "table_foreign_key_5" after deleting data from "table_unique_1". Notice that the child rows with id values 0 and 1 were automatically deleted:

SELECT id FROM table_foreign_key_5;

+----+
| ID |
+----+
| 2  |
+----+

The ON DELETE SET NULL option sets foreign key column values to NULL when the referenced parent row is deleted. This preserves the child rows while removing the invalid foreign key reference. The foreign key column must allow NULL values for this option to work.

CREATE TABLE table_foreign_key_6
(
    id NUMBER(1,0)
,   CONSTRAINT cr_table_foreign_key_6_id FOREIGN KEY (id) REFERENCES table_unique_1 (id) ON DELETE SET NULL
);

The "ON DELETE SET NULL" option means that if a row is deleted from the "table_unique_1" table, then the foreign key column values in the "table_foreign_key_6" table that reference it will be set to NULL, preserving the child rows.

Verify the creation of the constraint:

SELECT constraint_name, constraint_type, generated, delete_rule
FROM user_constraints
WHERE table_name = 'TABLE_FOREIGN_KEY_6';

Query result:

+---------------------------+-----------------+----------------+-------------+
| CONSTRAINT_NAME           | CONSTRAINT_TYPE | GENERATED      | DELETE_RULE |
+---------------------------+-----------------+----------------+-------------+
| CR_TABLE_FOREIGN_KEY_6_ID | R               | GENERATED NAME | SET NULL    |
+---------------------------+-----------------+----------------+-------------+

Demonstration:
Insert test data into both tables:

INSERT INTO table_unique_1 (id, id1, id2) VALUES (0,0,0);
INSERT INTO table_unique_1 (id, id1, id2) VALUES (1,0,1);
INSERT INTO table_unique_1 (id, id1, id2) VALUES (2,1,0);

INSERT INTO table_foreign_key_6 (id) VALUES (0);
INSERT INTO table_foreign_key_6 (id) VALUES (1);
INSERT INTO table_foreign_key_6 (id) VALUES (2);

Delete parent rows to trigger the SET NULL action:

DELETE FROM table_unique_1 WHERE id < 2;

Check the data in "table_foreign_key_6" after deleting data from "table_unique_1". Notice that the child rows remain but their foreign key values are set to NULL:

SELECT id FROM table_foreign_key_6;

+--------+
| ID     |
+--------+
| (null) |
| (null) |
| 2      |
+--------+

Disabling a foreign key constraint temporarily suspends referential integrity checking without removing the constraint definition. This is useful during bulk data operations, data migration, or troubleshooting. The constraint remains in the data dictionary but is not enforced.

ALTER TABLE table_foreign_key_1 DISABLE CONSTRAINT cr_table_foreign_key_1_id;

Important: When a foreign key constraint is disabled, Oracle does not validate data inserted or updated in the child table. Re-enabling the constraint will validate all existing data, and the operation will fail if any referential integrity violations are found.

Enabling a previously disabled foreign key constraint restores referential integrity checking. Oracle validates all existing data in the child table against the parent table when the constraint is enabled.

ALTER TABLE table_foreign_key_1 ENABLE CONSTRAINT cr_table_foreign_key_1_id;

Note: If any existing data violates the foreign key constraint, the ENABLE operation will fail with an error. You must either correct the data or use the ENABLE NOVALIDATE option to enable the constraint without validating existing data (not recommended for data integrity).

Dropping a foreign key constraint permanently removes it from the table definition. This operation cannot be undone, and the constraint must be recreated if needed later.

ALTER TABLE table_foreign_key_1 DROP CONSTRAINT cr_table_foreign_key_1_id;

Warning: All foreign key constraints referencing a table must be dropped before the referenced table can be dropped. Oracle prevents dropping tables that are referenced by foreign keys to maintain referential integrity:

DROP TABLE table_unique_1;

Query result:

Error starting at line 1 in command:
DROP TABLE TABLE_UNIQUE_1

Error report:
SQL Error: ORA-02449: unique/primary keys in table referenced by foreign keys.
*Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table.
*Action: Before performing the above operation, drop the foreign key constraints in the other tables.
         You can see which constraints reference a table by issuing the following command:
         SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TABNAM';

Alternative: You can use the CASCADE CONSTRAINTS option with DROP TABLE to automatically drop all foreign key constraints that reference the table being dropped:

DROP TABLE table_unique_1 CASCADE CONSTRAINTS;