SQL FOREIGN KEY Constraint

The SQL Foreign Key is used to enforce the link between two tables and it will be a column or a collection of columns which refers to the primary key of another table.

SQL FOREIGN KEY Constraint ensures the referential integrity it means whenever a foreign key value is used it must reference a valid, existing primary key in the parent table. 

Here is an Orders Table, which has StatusID as Foreign Key that refers to OrderStatus Table.

Foreign Key constraint prevents to insert or update invalid data in primary table [ Orders ] which does not exist into another table [OrderStatus] and it always maintain relationship between these two tables.

Syntax: Create Foreign Key on Create Table:

The below SQL script creates a Foreign Key Constraint  ‘FK_OrderOrderStatus’ on StatusID column which refers to primary key column of another table [OrderStatus]

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

     StatusID int, 

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50),

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED (StatusID ASC), 

)

Syntax: Create Foreign Key on Alter Table:

The below SQL creates a Foreign Key Constraint on a column StatusID and the Orders table is already created.

ALTER TABLE Orders

ADD CONSTRAINT FK_OrderOrderStatus

FOREIGN KEY (StatusID) REFERENCES OrderStatus(StatusID);

Syntax: Drop Foreign Key:

The below SQL is used to drop the Foreign Key constraint from Table

ALTER TABLE Orders

DROP CONSTRAINT FK_OrderOrderStatus;

SQL Primary Key Constraint

SQL Primary Key is a single column or a group of column which uniquely identify each record of table. Primary Key Column is always Not NULL column and it does not accept NULL value.

By Default, SQL creates a clustered index on primary key, which defines the order of record.

Syntax: Create Primary Key on Create Table

The below SQL will create a primary key on column StatusID on OrderStatus Table

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50)    

)

It creates primary key on StatusID and by default it creates clustered Index ‘PK__OrderSta__C8EE20434A6E8ADF’ on StatusID, as you see below

There is another way to create primary key constraint on table and also you can give primary key name ‘OrderStatus_PK’

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50),

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED ( StatusID ASC), 

)

Syntax: Create Primary Key on Alter Table

By using Alter SQL query, Primary key constraint can be created on existing table’s column

ALTER TABLE OrderStatus

ADD PRIMARY KEY (StatusID);

Syntax: Drop Primary Key from Table

By using below SQL, we can drop the primary key constraint from OrderStatus Table.

ALTER TABLE OrderStatus

DROP CONSTRAINT [OrderStatus_PK]

How to create SQL sequence object with example

This article explains how to create SQL sequence object and how to generate unique value for column by using sequence object during insertion or updating of data.

SQL Server has SEQUENCE object and it is a user-defined schema bound object that generates a sequence of numeric values on below sequence object’s specification.

·        START WITH

·        INCREMENT BY

·        MINVALUE

     ·        MAXVALUE

Create a sequence object that generates sequence number increases by 1 and start with 1

    CREATE SEQUENCE Project.ProjectNumber 

    START WITH 1

    INCREMENT BY 1;

Run the below statement to get next sequence number.

SELECT NEXT VALUE FOR Project.ProjectNumber

Output:

1

You can drop the sequence object by using DROP Sequence statement

Drop SEQUENCE Project.ProjectNumber 

By default, sequence object is defined INT type and you can define in other datatype like tinyint, smallint, bigint, decimal and numeric

CREATE SEQUENCE Project.ProjectNumber  AS decimal(3,0) 

START WITH 1

INCREMENT BY 1;

Here is Table ProjectAssignement

Column	DataType
AssignmentNumber	int
ProjectID	int
UserID	int
AssignmentDate	DateTime

SQL Script:

Create Table Project.ProjectAssignement

     (

            AssignmentNumber int NOT NULL,

            ProjectID int NOT NULL,

            UserID int NOT NULL,

            AssignmentDate Datetime DEFAULT getdate()

            PRIMARY KEY (AssignmentNumber)

     )

For above Table, we can use Project.ProjectNumber Sequeunce object to generate unique number for AssignmentNumber Column

INSERT INTO Project.ProjectAssignement (AssignmentNumber,ProjectID,UserID)

VALUES (NEXT VALUE FOR Project.ProjectNumber, 1,1001);

INSERT INTO Project.ProjectAssignement (AssignmentNumber,ProjectID,UserID)

VALUES (NEXT VALUE FOR Project.ProjectNumber, 1,1002);

INSERT INTO blocking.ProjectAssignement (AssignmentNumber,ProjectID,UserID)

VALUES (NEXT VALUE FOR Project.ProjectNumber, 1,1003);

GO

Select * from Project.ProjectAssignement

Output:         

   

In other way, you can use sequence object to generate default value for AssignementNumber column

Create Table Project.ProjectAssignement

     (

            AssignmentNumber int NOT NULL DEFAULT NEXT VALUE FOR Project.ProjectNumber,

            ProjectID int NOT NULL,

            UserID int NOT NULL,

            AssignmentDate Datetime DEFAULT getdate()

            PRIMARY KEY (AssignmentNumber)

     )

Now run the below insert script

INSERT INTO Project.ProjectAssignement (ProjectID,UserID)

VALUES (6,1001);

INSERT INTO Project.ProjectAssignement (ProjectID,UserID)

VALUES (6,1002);

INSERT INTO Project.ProjectAssignement (ProjectID,UserID)

VALUES (6,1003);

GO

Select * from Project.ProjectAssignement

Output:

INCLUDE Columns in Non-Clustered Index

This article explains how to use INLCUDE clause while creating the non-clustered index and how it helps to improve the query performance.

Non-clustered index is used to improve the performance of queries which are not covered by clustered index and while creating of non-clustered index, we consider only those columns which are being part of WHERE/ORDER BY/GROUP BY/JOIN statement.

CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]

(

   [DepartmentCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [Primary]

In below SQL query, which fetches department information like name and location on based on input department code.

Select DepartmentName, DepartmentLocation From dbo.Department where DepartmentCode = @Code

In this query, the department record is quickly found with help of nonclustred index ‘DepartmentCode_IDX’ but to get department name and location information they have to fetch from table.

Built Non-Clustered Index-tree (B-Tree):

To improve the further performance of above select query, we can use INCLUSE clause in non-clustered index and specify the list of non-key columns which are part of Select statement like ‘ DepartmentName’, ‘DepartmentLocation’

CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]

(

   [DepartmentCode] ASC

) INCLUDE( DepartmentName, DepartmentLocation)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [Primary]

Built Non-Clustered Index-tree (B-Tree):

You can see the Include Columns are not part of all Index nodes expects leaf nodes so overall it is not increasing index tree’s size and benefit to have include columns, it adds the extra data at leaf node and it helps to get select columns value from index leaf nodes instead of fetching data from table so in this way it improves the performance of query.

Other related Post:

SQL: Clustered, Non-Clustered and Index on Multiple Columns