Creating primary Index

[rebelknight]

I have a table with no indexes or key constraints.

If I create a unique clustered index on a table ( CREATE UNIQUE CLUSTERED INDEX Table01(Col1) ) - is it in effect just like creating a primary clustered key constraint when creating a table?

 

[ptheriault]

Yes you are creating a Primary Key clustered.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

No, a PK can not have a NULL value a UNIQUE CONSTRAINT can have ONE NULL value. I like to ask this question in an interview ;-)

example

CREATE TABLE Table01(COL1 int)
CREATE UNIQUE CLUSTERED INDEX ixBlah on Table01(Col1)  

CREATE TABLE Table02(COL1 int primary key)

--no problem
insert Table01 values(null)

--both of these will fail
insert Table02 values(null)
insert Table01 values(null)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

there are basically 2 differences bewteen a PK and a Unique Constraint

1) You can only have 1 PK bu many Unique Constraints on a table
2) A PK cannot have a NULL value while a Unique Constraint can have 1 NULL value (I believe on Oracle it can have many NULL values)






Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

>>1) You can only have 1 PK bu many Unique Constraints on a table


Yes, but only 1 Unique clustered.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

true

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[rebelknight]

So is this index create the same as creating a primary key constraint?

CREATE UNIQUE CLUSTERED INDEX Table01(Col1) NOT NULL

 

[gmmastros]

Why are you concerned about creating an index the is 'like' a primary key? Why don't you just create a primary key?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rebelknight]

Sheer curiosity.