Primary Key

In this blog, we’ll discuss about  Primary key.

  1. Primary key is one of the most commonly in use constraint in MS SQL.
  2. Only 1 Primary key can be defined in a table.
  3. By default, the column of the table automatically become NOT NULL  if Primary key is defined with column name while creating the table.
  4. It can be reference by Foreign key to set the relation between tables.
  5. Primary key guarantees the value of the column to be NOT NULL and Unique.
  6. It can be defined on a single column or on composite columns in a table.
  7. By default, primary key creates a Unique Clustered Index in the table.
  8. On disabling the Clustered Index which was created with Primary key, the table will become not accessible. To get the table back we need to rebuild that index.
  9. On disabling the Non Clustered Index which was created with Primary key, the table will remain accessible but the Primary key constraint will stop working.It means the column value can be modified or new value can be insert as NULL or duplicate values. (Note : If the column value is corrupted with NULL or duplicate data, then we cannot rebuild the Non-Clustered index. Because when we try to rebuild the index it’ll activate the Primary key and also do a validation check on the existing data,so in this case it’ll fail to validate the data.)
  10. Primary key never sort the data. It is due to the behavior of the Unique Clustered Index.
  11. Primary key can also be created with Unique Non Clustered Index.
  12. In case we have a table without Primary key but clustered index defined on a column and later on we tried to add a new column and set it as Primary key; it’ll automatically create a unique non-clustered index on the Primary key instead of clustered index. This is the second case where Primary key is defined with non-clustered index.
  13. On deleting the Primary Key manually, it’ll drop the index associated with it(Clustered/Non-Clustered) and on deleting the Index associated with Primary key it’ll automatically drop the Primary key.
  14. We can define, Primary key and Clustered Index on different columns only if we define the Primary Key with Non-Clustered Index.
  15. Anybody can define primary key in existing table with data. It can be achieved by making the column NOT NULL and remove the duplicate values.
  16. Any need to change the column’s datatype or need to drop the column (Column which is having the Primary key) entirely will require first DROP of the PRIMARY constraint on the column prior to your action.

Examples

–Example1: Default Primary Key, which will create a Unique Clustered Index on ID column
create table test
(
   Id int not null primary key,
   name varchar(50) not null
)
–Example2: Default Primary Key, which will create a Unique Clustered Index on ID column & Auto NOT NULL will be set for ID column
create table test
(
   Id int primary key,
   name varchar(50) not null
)
–Example3: Primary Key with Unique Non-Clustered index on ID column
create table test
(
   Id int primary key nonclustered,
   name varchar(50) not null
)
–Example4: Primary Key on multiple columns / composite primary key
create table test
(
   Id int not null,
   name varchar(50) not null,
   primary key (Id,name)
)
–Example5: Primary Key creation with constraint keyword, which will create a Unique Clustered Index on ID column
create table test
(
  Id int not null,
  name varchar(50) not null,
  constraint PK_ID primary key(ID)
)
–Example6: Primary Key creation outside table defination. It’ll create a Primary key with name PK_ID and a unique index with name PK_ID
create table test
(
  Id int not null,
  name varchar(50) not null
)
GO
— It creates a unique clustered index
Alter table test add constraint [PK_ID] primary key(ID)
— It creates a unique non clustered index
Alter table test add constraint [PK_ID] primary key nonclustered(ID)

To check or verify the index created on the table, we can use the below command

SELECT *
FROM sys.indexes I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE
T.Name = ‘test’                        — Table name
AND S.Name = ‘dbo’               — Schema name/optional

To get the detail information about all the Primary key defined in all the tables in the current database. (Note : PK=Primary Key. In case the primary key is a composite type, then the below query shows 2 entries in the result set.)

SELECT
@@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS TableName,   
c.name ColumnName,
t1.name as ColDataType,
c.max_length as ColMaxLength,
c.precision as ColPrecision,
c.scale as ColScale,
(case c.is_identity when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_IdentityType,
(case c.is_nullable when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_NullableType,
kc.name ConstraintName,
(Case i.is_disabled When 0 Then ‘No’ When 1 Then ‘Yes’ End) As PK_Index_IsDisable,
(case i.is_unique when 1 Then ‘Yes’ When 0 Then ‘No’ End) AS PK_Index_IS_Unique,
(Case i.index_id When 0 Then ‘Heap’ When 1 Then ‘Clustered’ Else ‘NonClustered’ End) As PK_IndexType,
(Case i.type
When 0 Then ‘Heap’
When 1 Then ‘Clustered’
When 2 Then ‘Nonclustered’
When 3 Then ‘XML’
When 4 Then ‘Spatial’
When 5 Then ‘Clustered columnstore’             –Applies to: SQL Server 2014 & Above
When 6 Then ‘Nonclustered columnstore’    –Applies to: SQL Server 2012 & Above
When 7 Then ‘Nonclustered hash’                   –Applies to: SQL Server 2014 & Above
End) AS PK_IndexSubType,
(case ic.is_descending_key when 0 Then ‘ASC’ When 1 Then ‘DSC’ End) AS PK_Index_SortOrder,
(case ic.is_included_column when 0 Then ‘No’ When 1 Then ‘Yes’ End) AS Col_IsIncluded
from
sys.key_constraints kc
inner join sys.objects o
on o.object_id = kc.parent_object_id
inner join sys.indexes i        
on i.name = kc.name
AND i.is_primary_key=1                         — get only the indexes auto created with primary key
inner join sys.index_columns ic
on ic.object_id = kc.parent_object_id and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = kc.parent_object_id and c.column_id = ic.column_id
INNER JOIN sys.types t1                            — Get the column datatypes
ON c.user_type_id = t1.user_type_id
Where
kc.type=’PK’                                                  — For Primary=PF
order by 3,4

To drop or remove the Primary constraint , we need to provide the constraint name. If the constraint name is auto generated then get it GUI from the left side in SSMS under the table => Goto constraint => click on the constraint and drag it over the command window.

ALTER TABLE [dbo].[test] DROP CONSTRAINT [PK_ID]

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

One thought on “Primary Key

  1. Pingback: Integrity Constraint in SQL | Lessons I Learned from sql

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s