Sql Server material 5

Constraints: used to enforce the integrity of the data in the columns, SQL Server 2005 provides the following mechanisms to enforce the integrity of the data in column:

            -Not Null

            -Unique

            -Primary Key

            -Check

            -Default          

-Foreign Key

Not Null: If it is imposed on a column that column will not allow Null Values into it; this can be imposed on any no of columns.

            -CREATE TABLE <table_name>(

             column_name1 <dtype> [width] [Not Null],

             column_name1 <dtype> [width] [Not Null],

             ………………….

             column_namen <dtype> [width] [Not Null])

Recreating the bank table with Not Null Constraint on it:

CREATE TABLE Bank(Custid int Not Null, Cname varchar(50), Bal decimal(7,2) Not Null)

After creating this if we try to insert a null value into the Custid or Bal columns it will restrict us:

            INSERT INTO Bank VALUES (NULL, ‘RAJU’, 3500)

            INSERT INTO Bank (CUSTID, CNAME) VALUES (101, ‘RAVI’)

The drawback with Not Null Constraint is even if it restricts null values it will not restrict duplicate values, if they has to be restricted we use the Unique Consraints.

Unique: If it is imposed on a column or columns they will not allow duplicate Values into it.

Note: Unique, Primary Key, Check and Foreign Key Constraints can be imposed in two different ways:

            -Column Level Definition

            -Table Level Definition

Column Level Definition: In this case the constraint definition is immediately followed after the column definition. The syntax is:

           

            -CREATE TABLE <table_name>(

             column_name1 <dtype> [width] [ [Constraint <Name>] <Type> ],

             column_name1 <dtype> [width] [ [Constraint <Name>] <Type> ],

             ………………….

             column_namen <dtype> [width] [ [Constraint <Name>] <Type> ],

Recreating the bank table with Unique Constraint on it:

CREATE TABLE Bank(Custid int Unique, Cname varchar(50), Bal decimal(7,2) Not Null)

After creating this if we try to insert a duplicate value into the Custid column it will restrict us:

            INSERT INTO Bank VALUES (101, ‘RAJU’, 3500)

            INSERT INTO Bank VALUES (101, ‘RAVI’, 4500)

Note: Internally Unique, Primary Key, Check and Foreign Key Constraints are identified by using some unique name which has to be given by us or else the system will give a name, so when we violate these constraints it will show the name of the constraint in the error message, by seeing which we require to identify on which column we are getting the problem, but if the table is not created by us or we don’t remember the structure of the table we cannot identify. So it is advised to give a name to the constraint so that when it violates the error message shows the name of the constraint using which we can easily identify where the violation has been done.

While giving a name to the Constraint they follow some conventions like:

                        <ColumnName_ConstraintType>

                        <TableName_ColumnName_ConstraintType>

Recreating the bank table with Unique Constraint by giving a name to it:

CREATE TABLE Bank(Custid int Constraint Cusid_UQ Unique, Cname varchar(50), Bal decimal(7,2) Not Null)

Table Level Definition: In this case the constraint definition is immediately followed after the column definition. The syntax is:

           

            -CREATE TABLE <table_name>(

             column_name1 <dtype> [width],

             column_name1 <dtype> [width],

             ………………….

             column_namen <dtype> [width],

 [ [Constraint <Name>] <Type> (<Collist>)],

…………………….)

Note: A Not Null Constraint Cannot be defined table level.

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Cusid_UQ Unique(Custid))

-In this case because the constraint is defined in the end of all the columns it cannot understand to which column the constraint depends so we need to specify the column name beside the constraint for identification.

-There will not be any difference in execution whether the constraint is defined in table level or column level.

-When we define a constraint in table level we can define composite constraint i.e. a single constraint on multiple columns.

CREATE TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint CC_BC_UQ Unique(CityCode, BranchCode))

INSERT INTO BankDetails Values(‘C1’, ‘B1’)

INSERT INTO BankDetails Values(‘C1’, ‘B2’)

INSERT INTO BankDetails Values(‘C1’, ‘B3’)

INSERT INTO BankDetails Values(‘C2’, ‘B1’)

INSERT INTO BankDetails Values(‘C2’, ‘B2’)

INSERT INTO BankDetails Values(‘C2’, ‘B3’)

-In this case all the statements are valid because a composite unique constraint checks the uniqueness on the combination of columns, but not on a single column.

The drawback with Unique Constraint is even if it restricts duplicate values it will allow a single null value in to the column. If we want to restricted duplicate values as well as null values we need to use Primary Key Constraint.


 <<< Previous                                                                                                                                Next >>>