SQL SERVER – Primary Key Constraints and Unique Key Constraints

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.

Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

Alter table with Primary Key:

ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL

Database, Software Development, SQL Coding Standards, SQL Constraint and Keys, SQL Download, SQL Error Messages, SQL Index, SQL Joins, SQL Scripts, SQL Server Security
Previous Post
SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case
Next Post
SQL SERVER – Function to Convert List to Table

Related Posts

97 Comments. Leave new

  • creating a blog
    March 8, 2013 4:40 pm

    Wonderful beat ! I would like to apprentice while you amend your website, how could i subscribe for a blog website?
    The account aided me a acceptable deal. I had been tiny bit acquainted
    of this your broadcast offered bright clear concept

    Reply
  • Hi ,

    Why unique key allows only NULL value when one NULL is different from other NULL.

    Reply
  • Hi Pinal,

    Happy Evening.

    First of all, your blog has been of great help to me in my SQL Server queries and application development. Almost all my queries are answered and its really great work done by you.

    I have got a query which i would like you to directly clarify.
    These days, i am into an retail chain application development with a HO and multiple BO which has distributed database. Since i want the primary key to be unique on data merge with Head Office database, i have kept the primary key to be nvarchar(50) in all the tables so that the primary key data would be something as mentioned below

    3 Characters of each : Company – Location – User – Division – Terminal – WorkStation – RowID(Identity Field of that table or a counter for generating unique number for each row)

    CO1-HOF-US1-DV1-TE1-WS1-1001
    CO2-BR1-US4-DV3-TE9-WS4-15201
    CO1-BR2-XYZ-FFL-TE8-WS2-250001

    This way when the data will merge, there wont be any clash in primary key index.
    The database will have millions of records on a future date.

    The complete database has been designed with very good normalization.

    I am planning to create a desktop based application as well as a web based application with the same data structure hosted on cloud server. Whether can i go ahead reluctantly or do i need to take some measure right now with nvarchar primary keys.

    Just want clarity, whether having an nvarchar(50) field as a primary key in all the tables which will be used for all multiple joins with every query will have a huge impact on Reports loading speed and on the database size?
    Is it recommended as per my application scenario? and if not then how should i achieve unique primary key with distributed database with 5-6 different factors (Company, Branch, Division, User, Terminal, WorkStation)

    Your quick response with clarity awaited.

    Reply
  • Usefull..Thanks

    Reply
  • Manoranjan Behera
    May 14, 2014 10:55 am

    Hi ,
    Can Foreign key reference to UNIQUE KEY ?
    Any one help me !

    Reply
  • Rohit Chougale
    May 5, 2015 12:02 pm

    Hi,
    As we know unique key only allow one null value.
    If we try to insert another null value then unique key constraint violated.
    But Null is unknown value, How can we compare one null value to another null value?
    And saying first entered null value is unique?
    Getting confused. Waiting for reply.
    Thanks in Advance.

    Reply
  • Remove the “not null” from the id column first.

    Reply

Leave a Reply