DATABASE & SQL/PLSQL

Constraints can be be enforced at two levels.

1. Column level
2. Table level

A constraints can be defined on a column at the time of creating a table. It can be created with the CREATE TABLE statement.

CREATE TABLE statement:

CREATE TABLE  table_name
column_name CONSTRAINTS  constraints_name constraints_type 
[, CONSTRAITS constraints_name constraints_type]

where, column_name is the name of the column on which the constraints is to be defined.

constraints_name is the name of the constraints to be created and must follow the rules for the identifier.

constraints_type is the type of constraints to be added.

ALTER TABLE statement:

ALTER TABLE  table_name
[WITH CHECK or WITH NOCHECK]
ADD CONSTRAINTS  constraints_name constraints_type

where, table_name is the name of the table that is to be altered for adding a constraints.

WITH CHECK and WITH NOCHECK specifies whether the existing data is to be checked or not checked for a newly added constraints or a re-enabled constraints

constraints_name specifies the name of the constraints to be created and must follow the rule for identifier.

constraints_type specifies the type of constraints

DROPPING CONSTRAINTS

A constraints dropped using the ALTER TABLE statement in the Query Analyzer.

All constraints defined in the table are dropped automatically when the table is dropped.