Re^6: (OT) Why SQL Sucks (with a little Perl to fix it)

Go back to that post and click the "response to Celada" link I had. I made it clear that the presense of a unique key is not enough. The duplicates are an issue in that case because the table is not in 3NF. If the table is is 3NF, then that unique constraint across all keys holds.

Cheers,
Ovid

New address of my CGI Course.

Comment on Re^6: (OT) Why SQL Sucks (with a little Perl to fix it)

Replies are listed 'Best First'.
Re^7: (OT) Why SQL Sucks (with a little Perl to fix it) by Theory (Beadle) on Dec 12, 2005 at 17:44 UTC
If the table is is 3NF, then that unique constraint across all keys holds. I don't think that's quite true. Consider this simple example: CREATE TABLE supplier ( id SERIAL PRIMARY KEY, name TEXT ); INSERT INTO supplier (name) VALUES ('Apple'); -- Computer INSERT INTO supplier (name) VALUES ('Microsoft'); INSERT INTO supplier (name) VALUES ('Apple'); -- Records SELECT * FROM supplier; id \| name ----+----------- 1 \| Apple 2 \| Microsoft 3 \| Apple This table is in the third normal form, in so far as I understand it, because "none of the non-primary key attributes is a fact about any other non-primary key attribute". However, you'll notice that there is a duplicate, in that "Apple" is listed twice. That's because there are two different companies named "Apple". So this table is technically in 3NF, but you cannot have a unique index across all of its columns. That's not to say that this is a good example, or that you couldn't work around this issue in various ways, but it demonstrates, I hope, that 3NF does not mandate that all columns be unique. —Theory	[reply]

Replies are listed 'Best First'.

Re^7: (OT) Why SQL Sucks (with a little Perl to fix it)
by Theory (Beadle) on Dec 12, 2005 at 17:44 UTC

If the table is is 3NF, then that unique constraint across all keys holds.

I don't think that's quite true. Consider this simple example:

CREATE TABLE supplier (
  id SERIAL PRIMARY KEY,
  name TEXT
);

INSERT INTO supplier (name) VALUES ('Apple');     -- Computer
INSERT INTO supplier (name) VALUES ('Microsoft');
INSERT INTO supplier (name) VALUES ('Apple');     -- Records

SELECT * FROM supplier;
 id |   name    
----+-----------
  1 | Apple
  2 | Microsoft
  3 | Apple

This table is in the third normal form, in so far as I understand it, because "none of the non-primary key attributes is a fact about any other non-primary key attribute". However, you'll notice that there is a duplicate, in that "Apple" is listed twice. That's because there are two different companies named "Apple". So this table is technically in 3NF, but you cannot have a unique index across all of its columns.

That's not to say that this is a good example, or that you couldn't work around this issue in various ways, but it demonstrates, I hope, that 3NF does not mandate that all columns be unique.

—Theory

[reply]