FOREIGN KEY Constraint

• 
• Defined at either the table level or the column level
• CREATE TABLE emp(employee_id numeric(6), last_name varchar(25) NOT NULL, email varchar(25), salary numeric(8,2), commission_pct numeric(2,2), hire_date DATE NOT NULL, ... department_id numeric(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES dept(deptno), CONSTRAINT emp_email_uk UNIQUE(email));

FOREIGN KEY Constraint - Keywords :

• FOREIGN KEY : Defines the column in the child table at the table-constraint level
• REFERENCES : Identifies the table and column in the parent table
• ON DELETE CASCADE : Deletes the dependent rows in the child table when a row in the parent table is deleted
  
• ON DELETE SET NULL : Converts dependent foreign key values to null
  

Adding Constraints in SQL

NOT NULL Constraint :

• Ensures that null values are not permitted for the column
• CREATE TABLE emp(employee_id numeric(6)CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name varchar(20) NOT NULL,...);

UNIQUE Constraint :

• Unique constraint allow only unique values to be inserted in the column.
• No duplicates allowed e.g emailIDs
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name varchar(20) NOT NULL, Email_id varchar(20) UNIQUE,...);

PRIMARY KEY Constraint :

• Don’t allow duplicate values
  
• Don’t allow null values
• Only one primary key can be created per table
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk RIMARY KEY, first_name varchar(20),...);

FOREIGN KEY Constraint :

• Defined at either the table level or the column level : continued in the next post