Why ADD CONSTRAINT?

ADD CONSTRAINT used to adding constraints to existing table in database those uses to increase the accuracy and reliability of the table. By using ADD CONSTRAINT Statement, user can add constraint to the existing table.

How to ADD CONSTRAINT?

We can add constraints using ALTER TABLE Statement followed by ADD CONSTRAINT Command. Most used syntaxes for ADD CONSTRAINTs on existing table are listed as below -

ALTER TABLE table_name
  ADD CONSTRAINT primarykey_name
  PRIMARY KEY (existing_column_name); 

ALTER TABLE childtable
  ADD FOREIGN KEY (childtable_column_name)
  REFERENCES parent table (parenttable_column_name); 

ALTER table table_name
  ADD CONSTRAINT Compkey_name
  PRIMARY KEY (required_column_name1, required_column_name2); 

ALTER TABLE table_name
ADD UNIQUE (column_name); 

ALTER TABLE table_name
 ADD CONSTRAINT chk_name
 CHECK (condition); 

Scenario – Adding Primary key on existing table.

Requirement – Adding emp_name as Primary key on existing employee_details table which does not have a primary key column. The query was as follows -

ALTER TABLE employee_details
ADD CONSTRAINT pk_emp_name
PRIMARY KEY (emp_name);   

By executing above query, emp_name was added as primary key on employee_details table.

Scenario – Adding Foreign key on existing table.

Requirement – Adding dept_id as foreign key(part of primary_key on employee_details table) on existing department_details table. The query was as follows –

Let us consider department_details table was created before as below –

CREATE TABLE department_details ( 
   dept_id INT NOT NULL,
   dept_name VARCHAR (20),
   dept_location VARCHAR (20)
   );
    
ALTER TABLE department_details
ADD CONSTRAINT fk_key
FOREIGN KEY (dept_id)
REFERENCES employee_details (dept_id); 

By executing above query, we can add foreign key on existing department_details table

Scenario – Adding composite key on existing table.

Requirement – Adding employee as composite key on existing employee_details table which does not have a primary key column. The query was as follows -

ALTER TABLE employee_details
 ADD CONSTRAINT ck_employee
 PRIMARY KEY (emp_id, dept_id);    

By executing above query, we can add composite primary key on existing employee_details table.

Scenario – Adding unique key on existing table.

Requirement – Adding emp_name as unique key on existing employee_details table which does not have a primary key column. The query was as follows -

ALTER TABLE employee_details
 ADD CONSTRAINT uq_emp_name 
 UNIQUE KEY  (emp_name);  

By executing above query, emp_name was added as unique key on employee_details table.

Scenario – Adding Check constraint on existing table.

Requirement – Adding Check constraint that dept_id is 1000 or 2000. The query was as follows -

ALTER TABLE employee_details  
ADD CONSTRAINT chk_ dept_id
CHECK  (dept_id = 1000  OR dept_id = 2000);

By executing above query, we can create chk_dept_id on existing employee_details table.