- In our post “I/O Constraint” and “Business Constraints” we had seen how we can add constraints at the time of table creation using Create table command. If you have not read our previous post then you are strongly suggested to read that post first before moving on.
- 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.
ALTER TABLE <table_name> ADD CONSTRAINT <const_name> <cont type>;
ALTER TABLE student ADD CONSTRAINT pk_roll_no PRIMARY KEY(roll_no) ;
ALTER TABLE <table name> ADD CONSTRAINT <cont_name> FOREIGN KEY (column) REFERENCES <master_table> (column);
ALTER TABLE student ADD CONSTRAINT Fk_roll_no FOREIGN KEY (roll_no) REFERENCES student (roll_no) ;
ALTER TABLE <table name> MODIFY <column name> UNIQUE/NOT NULL;
ALTER TABLE student MODIFY(name NOT NULL);
ALTER TABLE student MODIFY(mob UNIQUE);
ALTER TABLE <table_name>ADD CHECK <expression>;
ALTER TABLE emp ADD CHECK(Sal>1000);
ALTER TABLE <table name> DROP CONSTRAINT <cons_name>;
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.