• We can also add or modify constraints using Alter table command on existing table. In this post we will focus on how alter table command can be helpful if we need to add constraints later on existing table.
  • Data constraint can also be defined by using alter table command.
  • When we require to define constraints on existing table then alter table command can be helpful.
  • At the time of defining a constraint if records are available in the table that violets the specific constraint rules then it cannot be defined.
  • First we need to remove invalid records from the table that does not satisfy constraint.

Primary key:

  • Syntax:
ALTER TABLE <table_name> ADD CONSTRAINT <const_name> <cont type>;
  • Example:
ALTER TABLE student ADD CONSTRAINT pk_roll_no PRIMARY KEY(roll_no) ;

Foreign key:

  • Syntax:
ALTER TABLE <table name> ADD CONSTRAINT <cont_name> FOREIGN KEY (column) REFERENCES <master_table> (column);
  • Example:
ALTER TABLE student ADD CONSTRAINT Fk_roll_no FOREIGN KEY (roll_no) REFERENCES student (roll_no) ;

Unique/ Not Null Constraint:

  • Syntax:
ALTER TABLE <table name> MODIFY <column name>   UNIQUE/NOT NULL;
  • Example:
ALTER TABLE student MODIFY(name NOT NULL);
ALTER TABLE student MODIFY(mob UNIQUE);

Check Constraint:

  • Syntax:
ALTER TABLE <table_name>ADD CHECK <expression>;
  • Example:
ALTER TABLE emp ADD CHECK(Sal>1000);

Dropping Constraints:

  • Syntax:
ALTER TABLE <table name> DROP CONSTRAINT <cons_name>;
  • Example:
ALTER TABLE student DROP CONSTRAINT pk_roll_no;
  • If you have not assigned constraint name at the time of constraint definition and wish to drop constraint later than you can use oracle’s built-in table USER_CONSTRAINTS to and have a look at constraint information such as constraint name, constraint type etc.