-
Exact difference between primary key and unique + not nul
hi friends,
what is the difference between primary key column and not null + unique column.
I know one difference is a table can have only one primary key and any number of not null + unique key columns.
I think the functionality is same for the both type of columns.
Any other differences that you know??
by madhu
-
Since you get an index for free, the PK index will be one-to-one for values. For unique, given that nulls are not indexed in this case, you may have fewer indexed values.
-
clustered index for primary key doubt?
I heard that a clustered index will be created automatically
for primary key. As per my knowledge cluster means combination of two common columns of two tables. But how does a primary key creates the
cluster index? for which cluster it creates the index?
with thanks
-
Originally Posted by madhugp
I heard that a clustered index will be created automatically
for primary key. As per my knowledge cluster means combination of two common columns of two tables. But how does a primary key creates the
cluster index? for which cluster it creates the index?
"Clustered Index" is a SQL Server concept - closest thing in Oracle is an IOT e.g. Index Organized Table.
A PK constraint requires to have a unique index on a not nul column (or set of columns)
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.
-
a PK is a UNIQUE plus NOT NULL (you get a bonus index)...ie no duplicates no NULLS
a Unique index/column only prevents duplicates but would allow NULLs.
Looking for the greatest evil in the world? Look in the mirror.
-
Let me spice this one a little - even when risking to add some confusion.
PK constraints do not require a unique index, you can create a PK on a non-unique index. Moreover, in some cases it is mandatory to do so - like when you are asking Oracle to defer contraints checking until the end of the transaction.
After you digest this one start thinking about the crazy (and useful) things you can do manipulating VALIDATE/NOVALIDATE and ENABLE/DISABLE option.
So, does in general a PK comes alongside a unique index? Yes... but that does not means it has to be unique.
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|