m3rajk — Wed, 30 Jul 2003 15:41:32 +0000

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.

JeevesBond — Tue, 22 Jul 2003 21:09:07 +0000

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

JeevesBond — Sat, 19 Jul 2003 16:57:14 +0000

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.

Mark Hensler — Fri, 18 Jul 2003 19:12:50 +0000

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

+----+----------+
| id | username |
+----+----------+
|  1 | mark     |
|  1 | m3rajk   |
|  2 | mmi      |
|  3 | mark     |
+----+----------+

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

m3rajk — Fri, 18 Jul 2003 16:01:17 +0000

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

I just did--two of 'em

mmi — Thu, 17 Jul 2003 07:19:35 +0000

Unique Key Largo and Primary Colors

PPSD - Post-Posting Stress Disorder :eek:

Mark Hensler — Thu, 17 Jul 2003 06:38:58 +0000

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

Agh! Never should have tried to answer this one.

mmi — Thu, 17 Jul 2003 05:11:19 +0000

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."

mairving — Thu, 17 Jul 2003 02:01:17 +0000

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.

Oh, for Francis Scott!

mmi — Wed, 16 Jul 2003 22:16:35 +0000

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.