SQL Server Constraints

SQL Server Constraints

-          - Primary Key

Uniquely identify the records from the table is called as primary key.

Primary Key column contains unique and NOT NULL value.

By default it will create the Clustered index on column.

-- Create Primary key at the time of creating table

CREATE TABLE cust_sample

(

      cust_id           int  PRIMARY KEY,

      cust_name   char(50)

)

CREATE TABLE cust_sample

(

      cust_id           int,

      cust_name   char(50),

      CONSTRAINT PK_cust_Sample_Cust_id PRIMARY KEY (cust_id)

)

-- ALTER Table for adding the primary key

ALTER TABLE cust_sample WITH NOCHECK

ADD CONSTRAINT PK_cust_Sample_Cust_id PRIMARY KEY CLUSTERED (cust_id)

WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)

ON PRIMARY ;

-- DROP Primary constraint

ALTER TABLE cust_sample

DROP CONSTRAINT PK_cust_Sample_Cust_id ;

-  Candidate Key

Other than primary if any other column having the unique and not null value to uniquely identify the records from the table is called as Candidate key.

- Composite Key

Combining more than one column to uniquely identify the  record from  the table called as Composite key.

CREATE TABLE factory_process

(

event_type   int,

event_time   datetime,

event_site   char(50),

event_desc   char(1024),

CONSTRAINT event_key PRIMARY KEY (event_type, event_time)

)

-  Unique Key

Unique key is same as the primary key but one time it will accept the null value in the column.

By default  it will create the non clustered index on the column.

CREATE TABLE cust_sample

(

      cust_id           int  ,

      cust_name   char(50) UNIQUE NONCLUSTERED

)

- Not null

Not allow the null into the column.

 CREATE TABLE cust_sample

(

      cust_id     int  PRIMARY KEY,

      cust_name   char(50) NOT NULL

)

- Check constraint

Allow the condition satisfied value into the column.

Check constrain can be able to apply on the multiple column based on the user definded function.

If any check constraint have the condition on integer value if user trying to insert the null value into the column SQL Server will accept the value without error.

-- Adding the chcek constrain on the column

CREATE TABLE cust_sample

(

cust_id            int      PRIMARY KEY,

cust_name         char(50),

cust_address         char(50),

cust_credit_limit   money,

CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )

)

-- Adding the chcek constrain on the table level

CREATE TABLE CheckTbl (col1 int, col2 int);

GO

CREATE FUNCTION CheckFnctn()

RETURNS int

AS

BEGIN

   DECLARE @retval int

   SELECT @retval = COUNT(*) FROM CheckTbl

   RETURN @retval

END;

GO

ALTER TABLE CheckTbl

ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );

- Foreign Key

Foreign key is the reference link of two tables. Primary key of one table refer a column of another table .That table column called as foreign key.

CREATE TABLE [dbo].[PurchaseOrderDetail]

(

    [ProductID] [int] NULL REFERENCES Production.Product(ProductID),

    [UnitPrice] [money] NULL,

    [OrderQty] [smallint] NULL,

    [ReceivedQty] [float] NULL

)

-  Alternative Key / Secondary key

This is same as the foreign key

- Default constraint

When no value supplied to the column at the time of INSERT and UPDATE statement execution SQL Server automatically take the default defined value into the column.
CREATE TABLE [dbo].[PurchaseOrderDetail]
(
[ProductID] [int] NULL ,
[UnitPrice] [money] NULL DEFAULT 0,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL
)