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

How to truncate system version table ( temporal table)

This blog explains how to truncate the system version enabled table (i.e. temporal table) and Stopping System-Versioning on a System-Versioned Temporal Table

If you want to truncate the temporal table, you will get below error message

truncate table dbo.Project

Msg 13545, Level 16, State 1, Line 21

Truncate failed on table 'dbo.Project' because it is not a supported operation on system-versioned tables.

You want to perform any specific DML operation on temporal table and you want to temporary disable system–versioned table or you don’t need any version table anymore.

You can simple use SYSTEM_VERSIONING = OFF option and history table will stop capturing the updates

-- Disable the system version table

ALTER TABLE dbo.Project SET (SYSTEM_VERSIONING = OFF);

--Truncate data

Truncate Table dbo.Project

--Enable the system version table

ALTER TABLE dbo.Project SET ( SYSTEM_VERSIONING = ON );   

How to create SQL NotNULL constraint

SQL Not NULL constraint ensures the columns cannot have NULL value, if you insert or update NULL value to this column, it throws exception

Cannot insert the value NULL into column 'StatusCode', table 'dbo.OrderStatus'; column does not allow nulls. INSERT fails.

Create NOT NULL constraint:

SQL constrain NOT NULL can be defined in create table script

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),  

)

In above, we define NOT NULL constraint for StatusCode Columns.

What is SQL Constraints

In this blog, we will discuss about the SQL constraints like Primary Key, Not NULL etc. and what type of constraints are available in SQL and their purposes.

SQL constrains is used to enforce the data rule while inserting or updating record in table. If rule is not satisfied, it will not allowed to complete the action. It ensures the data accuracy and reliability in the table.

Following SQL constraints are used in SQL: 

• Primary Key -  it uniquely identify each row in table but it cannot accept NULL value
• Unique Key -  it ensure the column’s value are not duplicate
• Foreign Key – it enforce the integrity constraints and make sure the referenced key is uniquely identify each row in the referenced table
• Check  - it ensure the inserted or updated value matched with given values
• Not NULL – it ensures the column cannot accept NULL value
• Default – set default value for a column, if value is not specified.

Thanks for visiting !!