CONSTRAINTS

Constraints enable you to further control how data is entered into a table and are used to restrict values that can be inserted into a field and to establish referential integrity

Constraint - is a mechanism use to protect the relationship between data within an Oracle table or the correspondence between data in two different tables.

NULL / NOT NULL - used to indicate if a field can be left blank when records are entered into a table.
PRIMARY KEY - used to uniquely identify every record in a table
FOREIGN KEY - used to link records of a table to the records of another table
UNIQUE - used to ensure that every value in a column is different
CHECK - used to set criterion for the data entered into a column.

PRIMARY KEY
NOT NULL
UNIQUE
CHECK
FOREIGN KEY
NO ACTION
CASCADE

Guidleines for primary keys and Foreign keys

No duplicate values are allowed in a primary key
Primary keys generally cannot be changed
Foreign keys are based on data values and are purely logical, not physical, pointers
A foreign keys value must match an existing primary key value or unique key value or else be null
You cannot define foreign keys without existing primary (unique keys)

Adding Constraints

Adding constraints to your tables will make them more efficient and increase the integrity of the data
Constraints are used to establish relationships between tables
Adds a constraint to a table

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name constraint 

Adds a new column to a table of the specified data type

ALTER TABLE table_name 
ADD table_column datatype 

Removing Constraints

Removes a constraint from a table

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name 

Changing Constraints

Changes the data type of a column to a new datatype

ALTER TABLE table_name 
MODIFY table_column 

Changes a constraint on a column

ALTER TABLE table_name 
MODIFY (column_name CONSTRAINT constraint_name constraint) 

Referential Integrity Constraints

A relationship database usually has relationships set up between its tables.
A referential integrity constraint is one in which a row in one table (with a foreign key) cannot exist unless a value (column) in that row refers to a primary key value (column) in another table.
This is the primary key/foreign key relationship between two tables

Cascading Deletes and Updates

In SQL Server there is an option available that allows us to CASCADE the Delete and Update operations
An "ordinary" Delete or Update would fail if any referenced rows would be orphaned
A "cascaded" Delete or Update would delete all the referenced rows as well.

Defining a Referential Integrity Constraint

First the column that is being referenced must be defined as a primary key
Lets suppose we have two tables: Products and Orders
and lets assume that the Products table has a primary key on the ProductID
The Orders table will be the referencing table and will contain the foreign key.

Applying on Creation

CREATE TABLE ORDERS 
( 
OrderID INTEGER CONSTRAINT constraint.name1 PRIMARY KEY 
Quantity INTEGER 
ProductID INTEGER CONSTRAINT constraint.name2 REFERENCES Products(ProductID) 
) 

Applying After Creation

ALTER TABLE ORDERS 
( 
ADD CONSTRAINT constraint.name2 FOREIGN KEY (ProductID) REFERENCES Products(ProductID) 
)