December 4, 2013 at 11:43 am
sqlvogel (12/4/2013)
...A primary key constraint is a uniqueness constraint. Would adding a third key as you suggest really make any difference to the question of which ought to be "primary"? Would it make any difference which I chose? I think not.
A primary key is indeed a uniquenss constraint. But expanded by the "functionality" to serve as a reference key that can be referred to in other tables.
In terms of uniquness there's no difference at all between a primary key and "unique not null" constraint.
Since uniqueness is all you refer to just forget the term primary key and stay with the "unique not null" constraint.
The "additional feature" doesn't seem to matter to you at all. So why complain about the "special name" given to it so that people like me who are interested in referential integrity can easily identify (and refer to) a single row based on business criteria?
PS: If all I had to do is to technically identify a single row, I'd go with the clustered index.
December 4, 2013 at 1:36 pm
LutzM (12/4/2013)
A primary key is indeed a uniquenss constraint. But expanded by the "functionality" to serve as a reference key that can be referred to in other tables.
That's incorrect. It's not necessary to create a primary key for referential integrity purposes. Referential Integrity is a good thing of course but you can enforce referential integrity against any key not just a "primary" one (actually a foreign key can reference columns that aren't key columns at all). It's not so unusual to have foreign keys referencing non-primary keys.
Clustered indexes have nothing to do with it either. The clustered index can be on any set of columns, not necessarily primary key and not necessarily unique either. You don't need a clustered index to "identify a row".
December 4, 2013 at 1:46 pm
sqlvogel (12/4/2013)
That's incorrect. It's not necessary to create a primary key for referential integrity purposes. Referential Integrity is a good thing of course but you can enforce referential integrity against any key not just a "primary" one (actually a foreign key can reference columns that aren't key columns at all). It's not so unusual to have foreign keys referencing non-primary keys.
Clustered indexes have nothing to do with it either. The clustered index can be on any set of columns, not necessarily primary key and not necessarily unique either. You don't need a clustered index to "identify a row".
Maybe I'm reading your statement wrong, but i read "actually a foreign key can reference columns that aren't key columns at all" as though you are implying you can create a foreign key against any column.
If that's what you meant, that's not correct, of course;
in both SQL and oracle, only columns with a Primary key or Unique Constraint can be used to create a foreign key for referencial integrity; As i remember it SQLite, and mySQL also follow those same rules. I can't think of any other DMBS systems off of the top of my head that allow otherwise, though.
Lowell
December 4, 2013 at 2:14 pm
Lowell (12/4/2013)
Maybe I'm reading your statement wrong, but i read "actually a foreign key can reference columns that aren't key columns at all" as though you are implying you can create a foreign key against any column.
SQL DBMSs (including SQL Server) allow you to create a foreign key against any set of columns with a uniqueness constraint but that may include nullable columns and proper superkeys (eg "nested" uniqueness constraints) which therefore are not keys. Keys are supposed to be non-nullable and irreducible as well as unique. In most DBMSs (not including SQL Server) nullable uniqueness constraints don't necessarily ensure rows are "unique" at all because if there's a null in some column(s) in the constraint then by default the uniqueness is not enforced for the other columns on the same row.
In MySQL a foreign key can reference columns which don't have a uniqueness constraint on them, which is an excellent feature that more DBMSs really ought to support. There's no good reason why an inclusion dependency constraint must always be associated with a uniqueness constraint (a foreign key is just a special case of an inclusion dependency constraint).
December 4, 2013 at 2:33 pm
In MySQL a foreign key can reference columns which don't have a uniqueness constraint on them, which is an excellent feature that more DBMSs really ought to support. There's no good reason why an inclusion dependency constraint must always be associated with a uniqueness constraint (a foreign key is just a special case of an inclusion dependency constraint).
That actually sounds like a really bad and dangerous idea to me. What happens if you use cascading RI? What happens to the child rows for ON DELETE CASCADE??? Does it delete the children even though they have the same value referenced in a different parent? I am either misunderstanding what you are saying or I will make sure I avoid MySQL like the plague.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 5, 2013 at 8:59 am
LutzM (12/2/2013)
On the other side, there's the clustered index, which is unique and not null as well...
Objection - clustered indexes can be both non-unique and NULL. Yes, they should have a variety of properties (narrow, yes - ever-increasing, however, is for special cases), but those guidelines aren't enforced by the engine.
CREATE TABLE #test
(a TINYINT NULL
,b TINYINT NULL
)
INSERT INTO #test VALUES (1,1)
INSERT INTO #test VALUES (1,1)
INSERT INTO #test VALUES (NULL,1)
INSERT INTO #test VALUES (NULL,1)
INSERT INTO #test VALUES (1,NULL)
INSERT INTO #test VALUES (1,NULL)
INSERT INTO #test VALUES (NULL,NULL)
INSERT INTO #test VALUES (NULL,NULL)
SELECT * FROM #test
CREATE CLUSTERED INDEX Lousy ON #test
(a)
SELECT * FROM #test
DROP TABLE #test
Note that for SQL Server, a unique constraint is implemented as a unique index, and I agree with the above posters - put as many unique indexes (constraints) on your tables as you can.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply