Using ALTER Statement
Constraints can be created for existing tables using ALTER statement. The following instance describes this. Assume that the tables order_master, employee_master, item_master are created without constraints.
For add a primary key constraint on the field,
Syntax:
ALTER TABLE <tablename> ADD [CONSTRAINT <constraint name>] PRIMARY KEY (colname);
Example
ALTER TABLE employee_master ADD CONSTRAINT pk_empno PRIMARY KEY (empcode);
For add a unique constraint on the table,
Syntax:
ALTER TABLE <tablename> ADD [CONSTRAINT <constraint name>] UNIQUE (colname);
Example
ALTER TABLE item_master ADD CONSTRAINT unq_name UNIQUE (item_name); Check constraints are added using,
Syntax:
ALTER TABLE <tablename> ADD [CONSTRAINT <constraint name>] CHECK (colname <condition>);
Example
ALTER TABLE employee_master ADD CONSTRAINT ck_sal CHECK (salary BETWEEN 1000 AND 7000);
In order to add a referential integrity constraint use FOREIGN KEY and REFERENCES keyword.
Syntax:
ALTER TABLE <tablename> ADD [<CONSTRAINT <constraint name>] FOREIGN KEY (colname) REFERENCES <mastertable>(colname);
Example
ALTER TABLE order_master ADD CONSTRAINT fk_custcode FOREIGN KEY (ccode) REFERENCES cust_master(ccode);
Inserting a NOT NULL constraint using ALTER statement cannot be done straightly. Either MODIFIES or CHECK is needed to do this functionality.
Example
ALTER TABLE employee_master MODIFY (empname varchar2 (20) not null)