Adding Constraints to a table:
Ø A constraint can be added to a table
at any time after the table was created by using alter table statement, using
ADD clause.
Syntax:
Alter
Table <TableName>
Add
[Contraint <ConstraintName>]
Cons_type(column_name);
The Constraint name in the syntax is optional, but
recommended.
Guidelines:
Ø We can ADD, DROP, ENABLE or DISABLE
a constraint, but cannot modify the physical structure of the table.
Ø A NOT NULL can be added to existing
column by using the modify clause of the ALTER table statement.
Ø NOT NULL can be defined only when
the table contains no rows.
Example:
·
Alter Table Emp Add Constraint
Emp_mgr_fk Foreign key(mgr) References Emp(Empno);
Dropping Constraints:
Ø To Drop a constraint identity the
constraint the constraint name from the
o
USER_CONSTRAINTS
o
USER_CONS_COLUMNS
data dictionary views.
Ø The ALTER TABLE statement is used
with the drop clause.
Ø The CASCADE option of the DROP
Clause causes any dependent constraints also to be dropped.
Ø When a constraint is dropped, the
constraint is no longer enforced and is no longer available in the data
dictionary.
Syntax:
·
Alter Table <Table_Name>
DROP
PRIMARY
KEY/UNIQUE (column)/
CONSTRAINT
ConstraintName[CASCADE];
Example:
·
Alter
Table Dept drop primary key cascade;
·
Alter
Table Emp drop constraint Emp_Mgr_FK;
Enabling Constraints:
Ø The constraint can be enabled
without dropping in or re-creating it.
Ø The alter table statement with the
enable clause is used for the purpose.
Syntax:
·
Alter
Table <TableName>
Enable
Constraint <ConstraintName>;
Guidelines:
Ø Enabling a constraint applies to all
the data in the table at a time.
Ø When an UNIQUE or Primary key
constraint is ENABLED, the UNIQUE or Primary key index is automatically
created.
Ø The ENABLE clause can be used both
in create table as well as alter table statements.
Examples:
·
Alter Table Emp ENABLE Constraint
Emp_Empno_FK;
Viewing Constraints:
Ø To view all constraints on a table
query upon the data dictionary USER_CONSTRAINTS.
Ø The Codes that are revealed are …
C à CHECK
P à PRIMARY KEY
R à REFERENTIAL INTEGRITY
U à UNIQUE KEY
Example:
·
Select Constraint_name,
Constraint_type,Search_condtion From USER_CONSTRAINTS where table_name = ‘EMP’;
Viewing the columns Associated with Constraints:
Ø The Names of the columns that are
involved in constraints can be known by querying the USER_CONS_COLUMNS data
dictionary view.
Example:
·
Select
constraint_name, column_name from user_cons_columns where table_name
=’EMP’;