Constraints

Constraints as name suggest are rules for a table and columns that define what kind of data can be stored in the table.

Primay Key Constraint: Primary key is the most widely used constriant that is usually required in every table. A primary key consists of one or more columns of a table, combination of which will be unique in the table.

Syntax:

ALTER TABLE <table_name> ADD

CONSTRAINT <constraint_name>

PRIMARY KEY (

<column_name_1>,

<column_name_2>,...

<column_name_N> );

For ex:

ALTER TABLE Employee ADD

 CONSTRAINT Employee_pk

primary key (

 Emp_id );

Here:

<table_name>

<constraint_name> is the name of the primary key constraint, and

<column_name> is a column to use in the constraint.

is the name of the table.


Foreign Key Constraint: Foreign key constraints is the SQL way to maintain referential integrity. It helps in defining the parent- child relationship between the tables. Once a parent table column value is used in the child table it can't be deleted fron the parent table without deleting from the child table.

Syntax:

ALTER TABLE <table_name> ADD
CONSTRAINT <constraint_name>
FOREIGN KEY (
<column_name_1>,
<column_name_2>,…
<column_name_N> )
REFERENCES <referenced_table_name> (
<column_name_1>,
<column_name_2>,…
<column_name_N> );

For ex: 

ALTER TABLE  Employee ADD
CONSTRAINT employee_fk1
FOREIGN KEY (branch)
REFERENCES Department(branch);

Here: <table_name> is the name of the table to be constrained,
<constraint_name> is the name of the foreign key constraint,
<referenced_table_name> is the name of the table to be referenced,
<column_name> is a column that is being referenced