Oracle foreign key constraint child table, parent table, oracle Constraint
Create table employees (
Employee_id NUMBER (6 ),
Last_name VARCHAR2 (25) not null,
Email VARCHAR2 (25 ),
Salary NUMBER (8, 2 ),
Commission_pct NUMBER (2, 2 ),
Hire_date date not null,
...
Department_id NUMBER (4 ),
CONSTRAINT emp_dept_fk foreign key (department_id)
REFERENCES parameters (department_id ),
CONSTRAINT emp_email_uk UNIQUE (email ));
Foreign key: indicates the column in the child table at the table level.
REFERENCES: identifies columns in the parent table
On delete cascade: When a column in the parent table is deleted, the corresponding column in the child table is also deleted.
On delete set null: the columns in the subtable are empty.
Oracle foreign key constraints
Refer:
Alter table titles
ADD
CONSTRAINT FK_pub_id foreign key (pub_id) REFERENCES publishers (pub_id)
CONSTRAINT FK_pub_id foreign key (pub_id)
-- Pub_id fields in the subtable
REFERENCES publishers (pub_id)
-- Pub_id fields in the parent table
Generally, the same name is used. It can be different.
How to delete a table with a primary foreign key relationship in oracle
Delete the child table first and then the parent table. If the two tables have primary and foreign key constraints, drop table tablename cascade constraints.