SQL Server : Constraints

It is very important for the data in a database to be accurate, consistent and reliable. This implies that it is very crucial to enforce data integrity. Data integrity ensures the consistency and correctness of data stored in a database. It is broadly classified into the following categories.

• Entity Integrity - ensures that each row in a table is unique. It enforces integrity of the data contained in the columns, which uniquely identify the rows in a table.
• Domain Integrity - ensures that only valid ranges of values are allowed to be stored in a column. It can be enforced, by restricting the type of data, the range of values and the format of the data.
• Referential Integrity - ensures that the data in the database remains uniformly consistent, accurate and usable even after the data in it has been changed. It maintains the integrity of data by ensuring that the changes made in the parent table are also reflected in all the dependent tables.

Enforcing Data Integrity :

Incorporating business rules and specifying the relevant constraints can enforce data integrity. Business rules refer to specific policies followed by an organization for the smooth running of its business. Business rules ensure that the database stores accurate data in relation to the business policies.

For example an organization may have a personal policy, which states that the minimum salary of an employee is $500. This is a business rule.

Constraints refer to rules, which restrict the values that are inserted in the columns of a table. A constraint can either be created at the time of creating a table or can be added later. When a constraint is created after table creation, it checks the existing data. If the existing data does not conform to the rule being enforced by the constraint, then the constraint is rejected.

A constraint can be created using either of the following statements:

• CREATE TABLE statement.
• ALTER TABLE statement.

CREATE TABLE Statement :

A Constraint can be defined on a column at the time of creating a table. It can be created with the CREATE TABLE statement :

Syntax :

 
 CREATE TABLE table_name
  (Col_Name1 datatype CONSTRAINT constraint_name constraint_type, Col_Name2....) 

ALTER TABLE Statement :

A Constraint can also be defined on a column after a table has been created. This can be done using the ALTER TABLE statement.

Syntax :

 ALTER TABLE table_name
   ADD CONSTRAINT constraint_name constraint_type (fieldName)

CHECK CONSTRAINT :

A Check Contraint enforces domain integrity by restricting the values to be inserted in a column. It allows only valid values in a column. It is possible to defined multiple CHECK constraints on a single column.

Syntax :

 CREATE TABLE table_name
  (Col_name datatype [CONSTRAINT Const_name] CHECK( <Criteria> ), Col_name datatype.....)

 Example :

   1. Create table dept (dno int, dname char(10), Dsize int constraint ck_dept_dsize check (dsize>20))
   2. Insert into dept values(10, 'HR', 30)
   3. Insert into dept values(20, 'Admin', 15)
        

The rules regrading the creation of CHECK constraint are as follows:

• It can be created at the column level as well as table level.
• It is used to limit the values that can be inserted into a column.
• It can contain user-specified search condition.
• It cannot contain sub queries. A sub query i one or more select statements embedded within another select statement.
• It does not check the existing data into the table if created with the No Check Option
• It can reference other columns of the same table.

A Check constraint can also be added to an existing table by using the following syntax.

Syntax :

 ALTER TABLE table_name ADD CONSTRAINT const_name CHECK ( <criteria> )

Example :

  1. Insert into dept values(20, 'Admin', 15)
      --- Correct, since we removed the restriction in the previous example.
  2. Alter TABLE dept ADD CONSTRAINT ck_dept_dsize CHECK (dsize>20)
      --- Error, since one of the dsize value is not greater than 20
  3. Update dept set dsize=25 where dsize<20
      --- Modifying the dsize value for those records which are having dsize value less than 20
  4. Execute command. no 2 again. Now, it will execute successfl
 

UNIQUE CONSTRAINT :

UNIQUE constraints are used to enforce uniqueness on non-primary key columns. A Primary Key constraint column automatically included a restriction for uniqueness. However a unique constraint allows null values. It is not advisable to have columns with null values, though these are allowed. A null value is also validated, hence there can only be one record in the table with a null value. The table can't contain another row with null value. A unique column does not allow duplicate values but allows one null value.

Syntax :

 CREATE TABLE table_name (colname1 datatype CONSTRAINT <const_name> UNIQUE, 
                          colname2 datatype ------------------------.........)

Example :
=======
     1. create table emp (empid int constraint uk_emp_empid unique, ename char(10))
     2. insert into emp values(100, 'A')
     3. insert into emp values(100, 'B')
         ---- Error, duplicate value into empid column
     4. insert into emp(ename) values('B')
     5. insert into emp(ename) values('C')
         ---- Error, duplicate NULL value into empid column

Adding a UNIQUE constraint to an existing table:

 Syntax:
 ======
  
   ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> UNIQUE ( <columnname>)

Example :
=======

   Alter table emp Add constraint uk_emp_empid unique(empid) 

The rules regrading the creation of UNIQUE constraint are:

1. It does not allow two rows to have the same non-null values in a table.
2. It gets enforced automatically when a UNIQUE index is created.
3. Multiple UNIQUE constraints can be placed on a table.

To Be Continue...