Primary Key and Unique confusion

[arowa704]

hi,

AFter reading this paragraph from a book, I got confused:

 

\"While PRIMARY KEY forbids NULL\'s in the attributes of the key, UNIQUE permits them. Moreover, the rule that two tuples may not agree in all of a set of attributes declared UNIQUEmay be violated if one or more of the components involved have NULL as a value. In fact, it is even permitted for both tuples to have NULL in corresponding attributes of the UNIQUE key.\"

 

What I got from this:

1) PRIMARY KEY cannot have NULL

2) UNIQUE can have NULL

 

From Moreover on... is where I got confused.

1) 2 tuples can have NULL in an UNIQUE attribute, which is legal....

 

Another Question

A Primary Key is Unique indirectly...?

But I\'m assuming those two keywords can never be used together.

 

Correct me If i\'m wrong. Thanks in advance

[Rheal]

All the primery key is for is to tell the database which Field for it to sort on. The database can be sorted using various fields but the primiary key is the first field to sort.

[gizmola]

All the primery key is for is to tell the database which Field for it to sort on. The database can be sorted using various fields but the primiary key is the first field to sort.

 

I don\'t agree with this. A Primary key by definition must be able to uniquely identify any one row in the table.

 

As for NULL, it is often seen as being a property that exists independent of normal SQL rules. When a column is NULL, a value is unknown. Thus even a column guaranteed to be UNIQUE will allow for NULLs if the NULL property is set. For this reason, many SQL purists recommend that NULL be avoided.

 

As for having NULL in a primary key column... that is not allowed. A primary key must have a value and that value is guaranteed unique across the entire series of columns which comprise the primary key.