Sunday 28 June 2015

Is PRIMARY KEY Required NOT NULL

Introduction
As we all know that the PRIMARY key is the combination of UNIQUE and NOT NULL.
We got a funny example that we want to share it with you.

Example
Trying to create a table in this manner

CREATE TABLE temp_Table
   (
      EmpID     INT         PRIMARY KEY,
      EmpName   VARCHAR(50) NOT NULL
   );

Look here with EmpID I am not mentioned any NOT NULL constraint. So the question is the Primary Key is created or NOT?

The answer is YES

But now I am trying to create PRIMARY KEY by using ALTER statement.

CREATE TABLE temp_Table
   (
      EmpID     INT,
      EmpName   VARCHAR(50) NOT NULL
   );

ALTER TABLE temp_Table
ADD CONSTRAINT PK_EmpID  PRIMARY KEY(EmpID);

Here the Question is same. Is the PRIMARY KEY is Created or NOT?
Answer is NO

It gives Error:

Msg 8111, Level 16, State 1, Line 20
Cannot define PRIMARY KEY constraint on nullable column in table 'temp_Table'.
Msg 1750, Level 16, State 0, Line 20
Could not create constraint or index. See previous errors.


So, query in mind is that when we create the first example without NOT NULL the Primary Key is created but in case of Second Example it is not Created Why?

Explanation
When we are trying to create our first Example

CREATE TABLE temp_Table
   (
      EmpID     INT         PRIMARY KEY,
      EmpName   VARCHAR(50) NOT NULL
   );

Primary Key is created when the table is created, so in this case no need of NOT NULL in our syntax.

sp_columns temp_Table

index_name
index_description
index_keys
PK__temp_Tab__AF2DBA794975D443
clustered, unique, primary key located on PRIMARY
EmpID

In our Second Example the Table is created first with NULL definition of the columns EmpID.


CREATE TABLE temp_Table
   (
      EmpID     INT,
      EmpName   VARCHAR(50) NOT NULL
   );


SELECT name, isnullable
FROM   sys.syscolumns WHERE id=OBJECT_ID('temp_Table')

name
isnullable
EmpID
1
EmpName
0


So it is not possible to create Primary Key in columns which have NULL values. This reason the error came.

Msg 8111, Level 16, State 1, Line 20
Cannot define PRIMARY KEY constraint on nullable column in table 'temp_Table'.
Msg 1750, Level 16, State 0, Line 20
Could not create constraint or index. See previous errors.
     



Hope you like it.




Posted by: MR. JOYDEEP DAS



2 comments:

  1. This is a tricky and good one articale

    ReplyDelete
    Replies
    1. Thanks @Sukamal ...
      Trying to post some other Tricky thing ...like this. Hope you enjoy.

      Delete