SQL Table Constraints

 

Some of my readers mailed me that,

"Why you write simple article in your web. As you your web contains very good stuff… but some tropics are really simple."

It is facts. I have long list of friends from Linked In web. They send me regular mail to write different types of tropics. From them there is a huge number of junior professional and students. I have to serve them all. Students are a very important reader in my article and I think they really need guideline and I can't ignore them. I also take free class for them in weekends and try to share my knowledge with them. I really like it….

In this article I am trying to illustrate some points related to table constraints. It's not only useful for junior professional but also useful for all readers. So let's starts.

Data integrity rules falls into three categories:

1.    Entity

2.    Referential

3.    Domain

 

Entity integrity

 

Entity integrity ensures that the each row of the database is uniquely identified.  You can specify the PRIMARY KEY constraint in the table.

 

Referential Integrity

 

It ensure that the relationship between tables remain preserved as data is INSERTED, DELETED and MODIFIED.

We can ensure the referential integrity by FOREIGN KEY constraints.

 

Take an example of "Person" and "Orders" table:

The "Persons" table:

P_Id		LastName		FirstName		Address		City
1		Hansen		Ola		Timoteivn 10		Sandnes
2		Svendson		Tove		Borgvn 23		Sandnes
3		Pettersen		Kari		Storgt 20		Stavanger

The "Orders" table:

O_Id		OrderNo		P_Id
1		77895		3
2		44678		3
3		22456		2
4		24562		1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

 

SQL Statements

CREATETABLE Person

                        ( P_id            int                    NOTNULL     PRIMARYKEY,

                          LastName  Varchar(50)   NOTNULL,

                          FirstName  Varchar(50)   NOTNULL,

                         Address       Varchar(100) NULL,

                         City               Varchar(50)   NULL

                        )

GO

CREATETABLE Orders

                        ( O_Id        int NOTNULLPRIMARYKEY,

                          OrderNo  int NOTNULL,

                          P_Id        int FOREIGNKEY REFERENCES Persons(P_Id)

                        )

If the table objects are already created we can use the Alter statements to set the foreign key.

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)REFERENCES Persons(P_Id)

Also we can use CASCADE DELETE with referential integrity. In the example if we delete a specified person from the person table the corresponding Order will be automatically deleted. 

Example:

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)

REFERENCES Persons(P_Id)ONDELETECASCADE

We can also use CASCADE UPDATE with referential integrity. CASCADE specifies that the column will be updated when the referenced column is updated.

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)

REFERENCES Persons(P_Id)ONUPDATECASCADE

You can check if any foreign key is defined in your database with cascading actions usingsys.foreign_keys

SELECT    name AS [Constraint Name],

                    OBJECT_NAME(referenced_object_id) [Referenced Object],

                    OBJECT_NAME(parent_object_id) [Parent Object],

                    delete_referential_action_desc [ON DELETE],

                    update_referential_action_desc [ON UPDATE]

FROM        sys.foreign_keys

Domain Integrity

Domain integrity ensures that the values inside a database follow defined rules of values, range and format. Database can enforce the rules using

1.    CHECK Constraints

2.    UNIQUE Constraints

3.    DEFAULT Constraints

 

Check Constraints

Check constraints contain an expression the database will evaluate when we modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. We can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.

 

Example:

 

CREATETABLE Products_2

(

    ProductID int        PRIMARYKEY,

    UnitPrice money  CHECK(UnitPrice > 0 AND UnitPrice < 100)

)

 

OR

 

ALTERTABLE  Employees_2

ADD  CONSTRAINT CK_HireDate CHECK(hiredate <GETDATE())

 

The UNIQUE Constraints

 

The unique constraints determine that the columns values must be unique. Only one NULL is allowed by UNIQUE constraints.

 

Example:

 

CREATETABLE Products_2

(

    ProductID        int    PRIMARYKEY,

    ProductName  nvarchar(40)   Constraint IX_ProductName  UNIQUE

)

 

OR

ALTERTABLE Products_2

ADD  CONSTRAINT  IX_ProductName UNIQUE(ProductName)

Default Constraints

Default constraints apply a value to a column when an INSERT statement does not specify the value for the column. Although default constraints do not enforce a rule like the other constraints we have seen, they do provide the proper values to keep domain integrity intact. A default can assign a constant value, the value of a system function, or NULL to a column. You can use a default on any column except IDENTITY columns and columns of type timestamp.

Example:

CREATETABLE Orders_2

(

    OrderID          int     IDENTITY    NOTNULL,

    EmployeeID   int                         NOTNULL,

    OrderDate     datetime               NULL  DEFAULT(GETDATE()),

    Freight            money                  NULL  DEFAULT(0)CHECK(Freight >= 0),

    ShipAddress  nvarchar(60)       NULL  DEFAULT('NO SHIPPING ADDRESS'),

    EnteredBy      nvarchar(60)        NOTNULL DEFAULT(SUSER_SNAME())

)

Constraint Maintenance

After creating the constraint, if you think that the you don't needed the constraints you can drop the constraints or disable/enable the constraints.

To DROP the constraints

ALTERTABLE Products

DROPCONSTRAINT CK_Products_UnitPrice

Disable/Enable Constraints

ALTERTABLE Products NOCHECKCONSTRAINT  CK_UnitsOnOrder

ALTERTABLE Products NOCHECKCONSTRAINT ALL

 

ALTERTABLE Products CHECKCONSTRAINT  CK_UnitsOnOrder

ALTERTABLE Products CHECKCONSTRAINT ALL

 

Hope you like it.

Posted by: MR. JOYDEEP DAS