What are Constraints?

• Column level constraints or "inline" which applies to a column in a table or view.
• Table level constraints or "out-of-line" which applies to a table or view as a whole.

Types of Constraints

• The NOT NULL constraint restricts a column such that it must contain a value.
• The Unique constraint forces the value in the column to be unique against all other entries in the same column of the table. Oracle creates an internal index in order to assist the performance of this process.
• A Check constraint is a constraint which can be used to do validation and cross checking.
• A Primary Key constraint sets a primary key for a table or view. A primary key is also unique for which Oracle creates an internal index.
• A Foreign Key constraint is a child entry in a sibling table for a primary key. A foreign key can be duplicated and Oracle does not automatically create an internal index for a foreign key column. In general foreign key indexes should be created manually in order to avoid locking issues and serious performance problems as a result.

• A REF constraint provides a pointer to an object, type or other table.

Table Constraints Syntax

CREATE TABLE Command Syntax Including Constraints

CREATE TABLE [ schema.] table (
	column [ CONSTRAINT constraint ]
	{
		[ NOT ] NULL | UNIQUE | CHECK (condition)
		| PRIMARY KEY
		| REFERENCES [ schema. ] object  [ (column) ]
			 [ ON DELETE { CASCADE | SET NULL } ]
	} [ constraint state ]
	[ CONSTRAINT constraint ] constraint spec ]
		[, [ CONSTRAINT constraint ] constraint spec ] ]);

constraint spec: 
	{ UNIQUE (column [, column name ])
	| CHECK (condition)
	| PRIMARY KEY (column [, column ])
	| FOREIGN KEY (column [, column ])
		REFERENCES [ schema.] object { (column [, column ] ) }
			[ ON DELETE { CASCADE | SET NULL } ] constraint state

constraint state: [ [ [ NOT ] DEFERRABLE ] [ INITIALLY { IMMEDIATE | DEFERRED } ]
[ ENABLE | DISABLE ] [ VALIDATE | NOVALIDATE ] [ RELY | NORELY ] [ using index ] [ EXCEPTIONS INTO [ schema.] table ] ]

using index: [ [ schema.] index { [ CREATE INDEX ... ] [ TABLESPACE tablespace ] ] } ... ]

ALTER TABLE Command Syntax Including Constraints

ALTER TABLE [ schema.] table
ADD CONSTRAINT constraint table level constraint
MODIFY CONSTRAINT constraint constraint state
RENAME CONSTRAINT constraint TO constraint
DROP {
	  CONSTRAINT constraint [ CASCADE ]
	| { PRIMARY KEY | UNIQUE (column [, column ] ) } [ CASCADE ] 
		[ { KEEP | DROP } INDEX ] 
            }

constraint state: [ [ [ NOT ] DEFERRABLE ] [ INITIALLY { IMMEDIATE | DEFERRED } ]
[ ENABLE | DISABLE ] [ VALIDATE | NOVALIDATE ] [ RELY | NORELY ] [ using index ] [ EXCEPTIONS INTO [ schema.] table ] ]

using index: [ [ schema.]index { [ CREATE INDEX ... ] [ TABLESPACE tablespace ] ] } ... ]