Restriction on Primary key

Question

Restriction on Primary key

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 4

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

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 (NULL,1)

INSERT INTO #test VALUES (1,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.