Is Primary key clustered index different from Unique key clustered index??

Perry Whittle (4/17/2010)
when referring to key constraints
Primary key does not allow NULLs
Unique key allows a single NULL value
so the 2 are different in that respect

I was just thinking about this, and it dawned on me that this is slightly inaccurate.

The PK can only be applied to columns that are defined as NOT NULL, whereas a unique index can be applied to columns that are defined as NULL. So, the net effect is as you described it above. But, if you were to try to insert a null into a PK column with this code:

declare @test-2 table (

ID INT PRIMARY KEY CLUSTERED,

name varchar(10)

)

insert into @test-2 values (1,'aaa')

insert into @test-2 values (NULL, 'aaa')

You would get this error:

Msg 515, Level 16, State 2, Line 7
Cannot insert the value NULL into column 'ID', table '@test'; column does not allow nulls. INSERT fails.

Which shows that it's not the PK but the column's NOT NULL definition that is preventing the insertion of a NULL value.

So, would it be safe to say that the only difference between these is that a PK can only be applied to columns defined as NOT NULL, while unique indexes can be applied to columns defined as NULL, but in this case there can only be one NULL value?

Or am I splitting hairs too finely?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

WayneS (4/20/2010)
So, would it be safe to say that the only difference between these is that a PK can only be applied to columns defined as NOT NULL, while unique indexes can be applied to columns defined as NULL

Statement is correct but, it is good to remember PKs are built on top of not-null columns because PKs are there to enforce referential integrity.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

WayneS (4/20/2010)
Perry Whittle (4/17/2010)
when referring to key constraints
Primary key does not allow NULLs
Unique key allows a single NULL value
so the 2 are different in that respect
I was just thinking about this, and it dawned on me that this is slightly inaccurate.

actually it is slightly.

WayneS (4/20/2010)
So, would it be safe to say that the only difference between these is that a PK can only be applied to columns defined as NOT NULL, while unique indexes can be applied to columns defined as NULL, but in this case there can only be one NULL value?
Or am I splitting hairs too finely?

Looking at the question the OP has a PK across 3 columns.

Hi,
I have a SQl table which has a primary key defined onthe three columns. The index description for the primary key states as 'clustered, unique, primary key on PRIMARY'.
My questions is now is the defined primary key a Clustered, unique primary key or just Clustered primary key??

These naturally will not allow NULL as they are participating in a PK. A table definition would have been more helpful here i think!

A unique key will allow NULL values and it depends on the columns it covers. A unique key on a single column will only allow a single NULL value. When covering more than 1 column the allowances are greater.

The thing is the PK is unique (it has to be) but a unique key is a different kettle of fish altogether as it can allow NULLs

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

Perry Whittle (4/20/2010)
The thing is the PK is unique (it has to be) but a unique key is a different kettle of fish altogether as it can allow NULLs

Yes!... wait a sec, No! 😀

A Null is not a value per se but the absence of a known value so... a unique-index still unique in terms of "values" 😎

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150467 More actions · Answer 9

ALZDBA (4/20/2010)
Can you use that kind of filtered index for DRI purposes?

Not for enforcement purposes, no - you need an unfiltered UNIQUE index/constraint or a PRIMARY KEY.

It can be used to add flexibility to the referencing table though:

USE tempdb;

GO

CREATE TABLE dbo.Parent

(

parent_id INTEGER NULL

);

GO

CREATE UNIQUE INDEX uq1

ON dbo.Parent (parent_id)

GO

CREATE TABLE dbo.Child

(

child_id INTEGER NULL,

parent_id INTEGER NULL

REFERENCES dbo.Parent (parent_id)

);

GO

CREATE UNIQUE INDEX uq1

ON dbo.Child (parent_id)

WHERE parent_id IS NOT NULL;

GO

INSERT dbo.Parent (parent_id) VALUES (100);

INSERT dbo.Child (child_id, parent_id) VALUES (1, 100);

GO

INSERT dbo.Parent (parent_id) VALUES (NULL);

INSERT dbo.Child (child_id, parent_id) VALUES (2, NULL);

GO

INSERT dbo.Child (child_id, parent_id) VALUES (3, NULL);

INSERT dbo.Child (child_id, parent_id) VALUES (4, NULL);

GO

INSERT dbo.Child (child_id, parent_id) VALUES (NULL, NULL);

GO

DROP TABLE dbo.Child;

DROP TABLE dbo.Parent;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 10

PaulB-TheOneAndOnly (4/20/2010)
Statement is correct but, it is good to remember PKs are built on top of not-null columns because PKs are there to enforce referential integrity.

While we are splitting hairs, I'm going to mention that a PK is not required to enforce referential integrity - the aforementioned UNIQUE index/constraint can be used too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

Paul White NZ (4/20/2010)
ALZDBA (4/20/2010)
Can you use that kind of filtered index for DRI purposes?
Not for enforcement purposes, no - you need an unfiltered UNIQUE index/constraint or a PRIMARY KEY.

I thought I was missing something in my double checks :Whistling:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 12

Paul White NZ (4/20/2010)
PaulB-TheOneAndOnly (4/20/2010)
Statement is correct but, it is good to remember PKs are built on top of not-null columns because PKs are there to enforce referential integrity.
While we are splitting hairs, I'm going to mention that a PK is not required to enforce referential integrity - the aforementioned UNIQUE index/constraint can be used too.

I know that's a pretty common practice but I'm sure some purist will say you can't have "referential integrity" and "null value" on the same phrase 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150467 More actions · Answer 13

PaulB-TheOneAndOnly (4/21/2010)
...but I'm sure some purist will say you can't have "referential integrity" and "null value" on the same phrase 😉

Yes, they probably will...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

to further my point and using Pauls kind example

the following would fail and hence, would only allow a single NULL value

USE tempdb;

GO

CREATE TABLE dbo.Parent

(

parent_id INTEGER NULL

);

GO

CREATE UNIQUE INDEX uq1

ON dbo.Parent (parent_id)

GO

INSERT dbo.Parent (parent_id) VALUES (100);

INSERT dbo.Parent (parent_id) VALUES (NULL);

drop table dbo.parent;

The following would be successful and would allow multiple NULL values

USE tempdb;

GO

CREATE TABLE dbo.Parent

(

parent_id INTEGER NULL,

name_idINTEGER NULL

);

GO

CREATE UNIQUE INDEX uq1

ON dbo.Parent (parent_id, name_id)

GO

INSERT dbo.Parent (parent_id, name_id) VALUES (100, 100);

INSERT dbo.Parent (parent_id, name_id) VALUES (NULL, 200);

INSERT dbo.Parent (parent_id, name_id) VALUES (200, NULL);

INSERT dbo.Parent (parent_id, name_id) VALUES (NULL, NULL);

INSERT dbo.Parent (parent_id, name_id) VALUES (300, NULL);

INSERT dbo.Parent (parent_id, name_id) VALUES (400, NULL);

drop table dbo.parent;

Whether i explained it well or not is anybodies opinion, but then i wasn't aware we were

splitting hairs. I just made an observation 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul White SSC Guru Points: 150467 More actions · Answer 15

Perry Whittle (4/21/2010)
WhetherIi explained it well or not is anybody's opinion, but then I wasn't aware we were splitting hairs. I just made an observation 😉

I blame Wayne - he's very picky :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi