DB Constraints

1. PRIMARY KEY

·         In Each Table there is only one Primary key will be Accepted.

·         If you need to use more than one column as primary key called Composite Primary key.

·         One table’s Primary key is used as another table’s Foreign Key.

·         If you give a columns as Primary key that column does not allow Null values.

·         If you give a columns as Primary key that column does not allow Duplicate values.

Syntax: 

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> <Column_name>

E.g.: 

Type 1:

            Create table Author (

                        AuthorCode varchar (20) constraint <Constraint_name> Primary key,

                        Name varchar (20)

)

Type 2:           

Create table Author (

                        AuthorCode varchar (20),

                        Name varchar (20)

Primary key (AuthorCode)

)

Type 3:

                        Alter table Author add Primary key (AuthorCode)

NOTE: The Constraint is identified by Specified name (given by you) or default name (given by DB). Names are used for manipulate the Constraints as ALTER, DELETE.

COMPOSITE PRIMARY KEY: 

Type 1:

Create table Author (

                        AuthorCode varchar (20),

                        Name varchar (20)

Primary key (AuthorCode, Name)

)          

Type 2:

                        Alter table Author add Primary key (AuthorCode, Name)

2. FOREIGN KEY 

·         In Each Table there is Multiple Foreign key columns has been accepted.

·         One table’s Primary key is used as another table’s Foreign key.

·         The foreign key datatype and length should be same as primary key column value.

·         If you give a columns as Foreign key that column allow Null values.

·         If you give a columns as Foreign key that column allow Duplicate values.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> (Column_name) References <Primary key table name> (Column_name)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20),

                        AuthorCode (20) references Author (AuthorCode)

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Foreign key (AuthorCode) references Author (AuthorCode)

)

Type 3:

                        Alter table Author add Foreign key (AuthorCode) references Author (AuthorCode)

Type 4:

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20),

                        Name varchar (20)

Foreign key (AuthorCode, Name)

)          

Type 5:

Alter table Book add Foreign key (AuthorCode, Name) references Author (AuthorCode, Name) 

3. UNIQUE

·         In Each Table there is Multiple Unique columns has been accepted.

·         If you give a columns as Unique that column allow Null values only once.

·         If you give a columns as Unique that Does Not allow Duplicate values.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> (Column_name) References <Primary key table name> (Column_name)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20) Unique,

                        AuthorCode varchar (20)

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Unique (BookName)

)

Type 3:

                        Alter table Author add Unique (AuthorCode)

Type 4:

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20),

                        Name varchar (20)

Unique (AuthorCode, Name)

)          

Type 5:

Alter table Book add Unique (AuthorCode, Name)

4. CHECK 

·         In Each Table there is Multiple Check columns has been accepted.

·         If you give a columns as Check that column you can define a validation.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> constraint

(Column_name_with_condition)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

                        Subject varchar (20) Check (Subject in (‘Tamil’, ‘English’)),

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Subject varchar (20),

Check (Subject in (‘Tamil’, ’English’)),

)

Type 3:

Alter table Author add Check (Subject in (‘Tamil’, ‘English’)) 

5. NOT NULL

·         In Each Table there is Multiple Not Null columns has been accepted.

·         If you give a columns as Not Null that column does not allow Null values.

·         If you give a columns as Not Null that column allow Duplicate values. 

Syntax:

Alter table <table_name> Alter Column <Column_name> <Datatype> Not Null.

E.g.:

Type 1:

            Create table Book (

                        BookName varchar (20) Not Null,

                        AuthorCode (20) references Author (AuthorCode)

)

Type 2:

                        Alter table Author alter column AuthorCode varchar (30) Not Null

Thanks and Regards, 

Pugal.