Is declaring an attribute of a table as UNIQUE equivalent to declaring it as PRIMARY KEY?
thanks a lot!
+4
A:
The different is: Primary key will create clustered index by default and only one PK can be existed in one table. Primary key can cover multiple columns (composite key)
Hery
2010-03-16 06:02:24
You can have unique constraints spanning multiple columns.
Thilo
2010-03-16 06:05:27
+1
A:
No both are not same but are similar, when a column is Unique it has unique values but it also permit one Null value in that column but Primary does not permit any null values. Primary Key can be used for reference in some other table.
You can have only one Primary key in a table but multiple Unique Key
When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.
- The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.
Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.
Happy Coding !!!
Ravia
2010-03-16 06:02:34
"UNIQUE constraints can accept NULL but just once": That contradicts what AvatarKava said: UNIQUE keys may be NULL, and multiple NULL values are permitted. Which one is it?
Thilo
2010-03-16 06:23:52
From: http://dev.mysql.com/doc/refman/5.0/en/create-index.htmlA UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.
AvatarKava
2010-03-16 06:30:40
The question is tagged "mysql", but I believe Oracle also allows multiple NULL values (two NULL are never considered equal). That seems to be the more natural convention.
Thilo
2010-03-16 06:35:03
A:
Nope. Making a column as unique is very different from making it a primary key or part of primary key.
netlogger
2010-03-16 06:03:13
+2
A:
Nope.
PRIMARY KEYs must be UNIQUE, but UNIQUE keys need not be primary. You can have multiple UNIQUE keys in a table.
The key difference is that PRIMARY keys can not have NULL values, as they must uniquely identify a row. UNIQUE keys may be NULL, and multiple NULL values are permitted (unless you're using an uncommon table engine like BDB).
AvatarKava
2010-03-16 06:03:15
I seem to recall trying to create a unique index on a table and not being able to because of multiple NULL values, but I must be tripping.
Duncan
2010-03-16 06:25:11
Might have been back in 4.x, Duncan - I believe they did have that constraint back then: http://dev.mysql.com/doc/refman/5.0/en/create-index.html
AvatarKava
2010-03-16 06:31:26
+1
A:
UNIQUE can still be NULL.
PRIMARY KEY means UNIQUE and NOT NULL and there can be only one PRIMARY KEY per table.
Thilo
2010-03-16 06:03:24