DEFAULT CONSTRAINT

            Using default constraint we can insert default value into a column, if no other value is specified for that particular column.

Applying constraint on a new table:

Syntax:

CREATE TABLE <Table name>

(

<Col 1> <Data type>,

<Col 2> <Data type>,

.

.

<Col n> <Data type> DEFAULT <Value>

)

Example:

CREATE TABLE DEFAULT_TB1

(

ID INT

,Name varchar(10) DEFAULT 'aa'

)

Data insertion:

Inserting value to the ‘Name’ column:

If we specified a value for name column then same value will be inserted.

INSERT INTO DEFAULT_TB1 (ID,Name) VALUES (102,'ab')

O/P: SELECT * FROM DEFAULT_TB1

Inserting default value to the ‘Name’ column:

If we are not specified a value for name column then default value will be inserted.

INSERT INTO DEFAULT_TB1 (ID) VALUES (102)

O/P: SELECT * FROM DEFAULT_TB1

Applying constraint on existing table:

Syntax:

ALTER TABLE <Table name> add constraint <Constraint name> DEFAUTL <Default value> FOR <Column name>

Example:

ALTER TABLE DEFAULT_TB2 ADD CONSTRAINT DFC_Name2 DEFAULT 'AA' FOR Name

O/P: SP_HELP DEFAULT_TB2

Drop constraint:

ALTER TABLE DEFAULT_TB DROP CONSTRAINT DFC_Name2

 

CHECK CONSTRAINT

            Using check constraint we can restrict the unwanted data insertion into a column.

Applying constraint on a new table:

Syntax:

CREATE TABLE <Table name>

(

<Col 1> <Data type>,

<Col 2> <Data type>,

.

.

<Col n> <Data type>,

CONSTRAINT <Constraint name> <Constraint type> (<Column name> <Required condition>)

)

Example:

EMP table (Shown below) is having salary column (Sal), I want to restrict the data insertion if salary is less than 5000.

Table creation:

CREATE TABLE CheckCon_Tbl1

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

,CONSTRAINT Sal_CHK CHECK (Sal > 5000)

)

Data insertion:

If we try to insert a record with salary less than 5000, error message will popup.

INSERT INTO CheckCon_Tbl VALUES (101,'A','a','M',10,4000)

Error message:

The INSERT statement conflicted with the CHECK constraint "Sal_CHK". The conflict occurred in database "Test", table "dbo.CheckCon_Tbl", column 'Sal'.

The statement has been terminated.

Applying constraint on existing table:

            If we need to apply check constraint on existing table, the column data should satisfy the condition.

Syntax:

ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> <Constraint type> (<Column name> <Required condition>)

Example:

Table creation:

CREATE TABLE CheckCon_Tbl2

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

)

Adding constraint:

ALTER TABLE CheckCon_Tbl ADD CONSTRAINT Sal_CHK2 CHECK (Sal > 5000)

Drop constraint:

ALTER TABLE CheckCon_Tbl DROP CONSTRAINT Sal_CHK2

FOREIGN KEY

            Using Primary/Unique key and foreign key we can create parent and child relationship between two table. Primary/Unique key table is parent and Foreign key table is child table.

Note:

• First parent table should create then only we can create child table.
• First parent table should populate then only we can populate child table.
• If we need to drop parent constraint, first we should drop child constraint then only we can drop parent constraint.
• If we need to delete data from parent table, first we need to delete related data from child table.
• If we need to drop parent table first we should drop child table or child table foreign key constraint.

 

Applying Foreign Key constraint on a new table:

Syntax:-

CREATE TABLE <Child Table name>

(<Column 1> <Data type>,

<Column 2> <Data type>,

.

.

.

<Column N> <Data type>,

CONSTRAINT <Constraint name> <Constraint type> (Child table column name) REFERENCES <Parent table name> (parent primary/Unique key Column name)

)

Example:

Parent table:

CREATE TABLE DEPT_PK

(

DID INT

,Name VARCHAR(20)

,Dept INT

,CONSTRAINT DEPT_PK1 PRIMARY KEY (Dept)

)

Child table:

CREATE TABLE EMP_FK

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

,CONSTRAINT DEPT_FK1 FOREIGN KEY (Dept) REFERENCES DEPT_PK(Dept)

)

  

Result: SP_HELP EMP_FK 

 

Applying Foreign key constraint on existing table:

Note: If we want to create foreign key constraint on a column, that column related data should be present in parent table or child table should be empty, other wise we cannot apply foreign key constraint.

Syntax:

ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> FOREIGN KEY (<Child table Column name>) REFERENCES <Parent Table name> (<Parent table column name>)

Example:

Child table creation:

CREATE TABLE EMP_FK2

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

)

Adding Foreign key constraint:

ALTER TABLE EMP_FK2 ADD CONSTRAINT DEPT_FK2 FOREIGN KEY (Dept) REFERENCES DEPT_PK(Dept)

Result:

Drop constraint:

Syntax:

ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name>

Example:

ALTER TABLE EMP_FK2 DROP CONSTRAINT DEPT_FK2