primary key question

Primary keys can also be auto_increment fields.

hmm.. That's a difficult question for me. I never had it explained to me either why we have primary keys. I just started to use them without question.

Tables can have many keys. And many unique keys. But I think by making a field a primary key, you're saying that this will be the primary method of identifying records in this table. And the primary method of linking data in this table to that table.

Anyone else have any insight?

There's a little info in here:

http://ememi.com/mmi/write/oracle.html

under "Unique Identifiers"

Yeah, I think that is right, Mark. Unique keys can also contain Null values I believe. All primary keys are unique but not all unique keys are primary.

I'm no db'er, but according to this doc:

https://aurora.vcu.edu/db2help/db2y0/frame3.htm#db2y043

"a unique key cannot contain null values"

And that only makes sense, doesn't it? I know techies like to play with the language, but if something is "unique" and yet "null" ...

Well, it would at least seem you couldn't have it in more than one row of that "key" column.

According to MS:
Unique Keys
Unique constraints can allow null values, whereas primary key constraints do not allow null values. Another consideration when deciding whether to use a primary or unique key is that tables can have multiple unique constraints but only one primary key.

It looks like most db's will allow a unique key to be a NULL value since NULL doesn't mean 0, it means nothing.

Most? - All I can gather from that is that M$ db programs don't?

But the question I have is: how can more than one row in a relational db have the same value (null or otherwise) and still be a unique identifier?

I suppose one could argue that the row simply won't be included in selects or joins or whatever other actions are performed, and so no problems would develop.

But I'm still left to wonder what is unique about a value (in this case "no value") that appears in more than one row.

Here's an argument that seems to agree with your position:

"As a unique key can have only one value of each entry and NULL does not have any value, you can argue if a unique key can have NULL-values."

from http://www.mainframeforum.com/t591525.html

That link didn't help mme any.

It looks like you're right in that it seems to depend on the program. These guys don't allow it:

http://www.ics.uci.edu/~ics184/netdb2/key.html

http://www.rsyntax.com/um_ch034.htm

http://www.db2.jp/db2manual/en_US/index.htm?openup=admin/c0004799.htm

Here's another saying "yes":

http://news.dcn-asu.ru/BOOKS/Oracle.Unleashed.Second.Edition/ch02/0025-0028.html

"Unlike primary keys, unique keys can contain null values."

BUT, at the end of that paragraph, they write:

"... however, for a row that contains a non-null value for the SSN attribute, the value must be unique to the relation."

What the heck does that mean?

I can't make any sense of the "Function Based Index Enhancements" in

http://www.oracle-base.com/Articles/9i/SQLNewFeatures9i.asp

Well anyway ... I guess you need to base your decision on the software you're working with. From what I can find:

IBM's DB2 - no
MS - apparently yes
Oracle - dunno

E.g., I can't fine the answer in these:

http://news.dcn-asu.ru/BOOKS/Oracle8.How.To/chap6_1.html

http://www.cse.unsw.edu.au/~cs9804/Oracle/faq.html

HERE! These guys say "yes" in Oracle:

http://www.emu.edu.tr/english/facilitiesservices/computercenter/bookslib/Oracle%20Unleashed/oun20fi.htm

"The UNIQUE constraint does not prevent null values from being inserted."

lol@mmi ... I think you've done enough researching on this topic. Stop stressing and go watch a movie.

Unique Key Largo and Primary Colors

PPSD - Post-Posting Stress Disorder :eek:

lolol. i like ppsd. i thinnk i have that

mmi-

the reason it's up to those who create the db as to if a unique key can hold a null value depends on how null is done. (undeclared as well)

if it's done right it looks like the feild doesn't exist for that entry.

but you're right. it does get confusing... btw: m$ does things like they want, and they screw up everything to boot... ie: winblows 95,98,me,xp,nt1-5,2k ...

it sounds to me like the db system optimizes seaches for primary keys, since i'm not going to allow 2 people to have the same name i can make that as well as the user identification number be primary keys (probably better to do that. right now i'm trying to get the profile side up, so i'll think about this in relation to the forum side later.

thanx for all the links mmi
and thanx to all for their input. it has helped me figure it out a little better

Becarefull there. You don't want to make the combination of those two fields to be the primary key... Consider the following:

'In the above table, `id` is not unique, nor is `username`. But the combination of `id` and `username` is unique.

Wow, I wish I could have found this thread earlier...Hopefully the following will help :

A primary key is used to provide a unique identifier for a record in the database, you can use anything as this identifier but it is far faster (and takes less memory) if all the RDBMS has to do is match two numbers together; obviously this cannot be null, if null was allowed in this field records could disappear (please say if this needs to be expanded upon ).

A field which dis-allows duplicates is not a primary key, therefore there is no reason why null should not be allowed.
Imagine a form, part of which has an optional field called Post Code, if the user fills in a post code, great but we don't want the same person signing up twice so it's only logical that we don't allow duplicates, but it's optional so we should allow nulls - otherwise the RDBMS is going to return an error.

Wow, glad to get that off my chest
Hope it helps, also remember that all DB systems are different, what I have said here is based on a working knowledge of Access and MySQL.

Wow, did I just kill the thread , or solve the problem ?

well. this is my first return since the post. you didn't kill it. you helped clarify for everyone. i think this might be a candidate for a sticky. it gives a lot of good links and discussion for those new to sql that is helpful in creating a db.