December 2, 2013 at 11:16 am
There is a restriction that we must have only one primary key per table. We can have composite primary key by defining primary key over 2 or more columns. Actually,, a primary key uniquely defines the table and if we have two or more columns which follow unique and not null features why can't we have two primary keys?? I want to know what is the main reason behind having only one primary key in a table??
December 2, 2013 at 11:39 am
two primary keys, by definition, would be repetitively redundant 😀 if you think about it.
essentially the primary key's main purpose is to be able to uniquely point to each specific row of data..
if you can describe the same data uniquely via two different ways, they are just describing the same data, right, since they'd identify the same rows uniquely?
in SQL server, the PK is used to define how the data is physically stored on disk.
Lowell
December 2, 2013 at 12:13 pm
A Primary Key is used to identify a single row based on the "business rules". This key is used in foreign key references to ensure logical consistency of the business data. Therefore, the PK needs to be unique and not null.
Just think about two tables Orders and OrderDetails:
Without a foreign key reference in the OrderDetails table to a corresponding Order in the Orders table there wouldn't be any inconsistency in terms of the database structure itself, but based on the business logic there's the risk of inconsistent data.
Therefore, a Primary Key / Foreign Key combination should be used to ensure logical data consistency.
Assuming, you'd have two PK's for a single table, which one would identify a single row? If both can be used, then there seems to be some room for improvement for the table design...
On the other side, there's the clustered index, which is unique and not null as well (as well as it should be narrow and ever increasing). But the purpose is the not related to the business rules but to the physical data storage. Data storage doesn't care about business rules, unless it's defined that way, meaning the Primary Key of a table is also the clustered index. But they're not related by definition, just by the default setting in SQL Server Management Studio -> a Primary Key, when selected, will be created as PRIMARY KEY CLUSTERED.
But you can have a Clustered Index that is different to the Primary Key.
Let's assume the following scenario:
A company (let's call it "Hire'n'Fire") has a table Employees where all the information about their staff is stored. Let's also assume they have a huge fluctuation. Every time a new person is hired, a new EmpId is added. This is based on a 20 digit random number.
This number is used to identify this person and it's also used in other tables. Therefore, this should be a primary key.
But maybe not the clustered index, since it's not ever increasing. It might be a good idea, to add a separate Identity column that can be used as a clustered index in order to avoid page splits and fragmentation.
All of a sudden we have two columns being unique and not null. 😉
But only one can be referenced as a foreign key. If there's the need to add a FK reference to the Identity column we just added, then there's something wrong with the database design...
December 2, 2013 at 12:15 pm
Lowell (12/2/2013)
two primary keys, by definition, would be repetitively redundant 😀 if you think about it.essentially the primary key's main purpose is to be able to uniquely point to each specific row of data..
if you can describe the same data uniquely via two different ways, they are just describing the same data, right, since they'd identify the same rows uniquely?
in SQL server, the PK is used to define how the data is physically stored on disk.
Not completely true. You could have the PK declared on a nonclustered index and have the clustered index on another column or set of columns that are used for numerous range queries.
December 2, 2013 at 12:16 pm
Lowell (12/2/2013)
...in SQL server, the PK is used to define how the data is physically stored on disk.
Objection, your Honor!
The physical storage on disk is based on the clustered index, which doesn't have to be the PK.
Here's an example:
CREATE TABLE [dbo].[Table_1](
[a] [int] NOT NULL,
[int] NOT NULL,
)
CREATE CLUSTERED INDEX CI_Table_1_a
ON dbo.[Table_1] (a);
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [PK_Table_1_1] PRIMARY KEY NONCLUSTERED
(
ASC
)
December 2, 2013 at 12:25 pm
LutzM (12/2/2013)
Objection, your Honor!The physical storage on disk is based on the clustered index, which doesn't have to be the PK.
sheesh, i know that, explained that lots of OTHER times, and still blurted that crap out when i know better.
Thanks!
Lowell
December 3, 2013 at 3:28 am
But you can have a primary key and then a number of different unique constraints. Just because there is more than one possible candidate key in the table doesn't limit you from putting constraints on every candidate key. In fact, I would. Defined uniqueness within SQL Server is pretty powerful for the query optimizer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2013 at 3:42 am
gautham.gn (12/2/2013)
There is a restriction that we must have only one primary key per table. We can have composite primary key by defining primary key over 2 or more columns. Actually,, a primary key uniquely defines the table and if we have two or more columns which follow unique and not null features why can't we have two primary keys?? I want to know what is the main reason behind having only one primary key in a table??
Good question. As a matter of principle and for most practical purposes you can have more than one "primary" key. True, there is a particular syntax - the PRIMARY KEY constraint - which in SQL can only be used once per table. The Standard SQL version of the PRIMARY KEY constraint is essentially just syntactical sugar however and has very little to do with the foundation concept of a primary key being a "preferred identifier" in a table. A table can have multiple keys and if it does have more than one key then the choice of which to call "primary" is only as significant as you want to make it.
Unfortunately software vendors have ignored the principle inherent in the relational model that all keys are equal and have the same function and features. Microsoft and other DBMS vendors over the years have attached certain technical features very specifically to the PRIMARY KEY syntax alone. This causes a lot of problems because the key chosen for one purpose isn't necessarily the best choice for another purpose. I for one think it has reached a point where this has become a significant limitation that probably ought to be removed - either by deprecating the PRIMARY KEY syntax altogether or by removing the limitation that it can only be used once per table. The conventions associated with primary keys are well entrenched in the industry though and change probably is not going to happen any time soon.
December 4, 2013 at 9:51 am
sqlvogel (12/4/2013)
gautham.gn (12/2/2013)
There is a restriction that we must have only one primary key per table. We can have composite primary key by defining primary key over 2 or more columns. Actually,, a primary key uniquely defines the table and if we have two or more columns which follow unique and not null features why can't we have two primary keys?? I want to know what is the main reason behind having only one primary key in a table??Good question. As a matter of principle and for most practical purposes you can have more than one "primary" key. True, there is a particular syntax - the PRIMARY KEY constraint - which in SQL can only be used once per table. The Standard SQL version of the PRIMARY KEY constraint is essentially just syntactical sugar however and has very little to do with the foundation concept of a primary key being a "preferred identifier" in a table. A table can have multiple keys and if it does have more than one key then the choice of which to call "primary" is only as significant as you want to make it.
Unfortunately software vendors have ignored the principle inherent in the relational model that all keys are equal and have the same function and features. Microsoft and other DBMS vendors over the years have attached certain technical features very specifically to the PRIMARY KEY syntax alone. This causes a lot of problems because the key chosen for one purpose isn't necessarily the best choice for another purpose. I for one think it has reached a point where this has become a significant limitation that probably ought to be removed - either by deprecating the PRIMARY KEY syntax altogether or by removing the limitation that it can only be used once per table. The conventions associated with primary keys are well entrenched in the industry though and change probably is not going to happen any time soon.
Even in logical data modeling there is a single primary key defined. Eliminating the primary key from the physical data model makes no sense. There can be only one primary key even if you have two or more candidate keys available. These other candidate keys can be declared as alternate keys using the unique and not null constraints.
December 4, 2013 at 10:29 am
Lynn Pettis (12/4/2013)
Even in logical data modeling there is a single primary key defined.
That depends on the methodology, the modelling notation used and the modeller's practice and intention. It also depends on the requirement. If all keys are of equal precendence in the reality being modelled and if there is no difference in the way the different keys are to be implemented then there is no reason why one key must or should be singled out in a logical model.
Eliminating the primary key from the physical data model makes no sense.
It makes perfect sense if the designation of a primary key would be superfluous anyway. As an example, a Marriages table with Husband and Wife as two candidate keys (for the sake of this example assume the scope is active, monogamous, male-female marriages). The rules of the business domain demand that no-one should be simultaneously married to more than one person so both Husband and Wife keys are equally important. Why should it be necessary to choose just one of them as "primary" when there is no basis in reality for that primacy and no practical difference arising from such a choice?
December 4, 2013 at 10:48 am
sqlvogel (12/4/2013)
Lynn Pettis (12/4/2013)
Even in logical data modeling there is a single primary key defined.That depends on the methodology, the modelling notation used and the modeller's practice and intention. It also depends on the requirement. If all keys are of equal precendence in the reality being modelled and if there is no difference in the way the different keys are to be implemented then there is no reason why one key must or should be singled out in a logical model.
Eliminating the primary key from the physical data model makes no sense.
It makes perfect sense if the designation of a primary key would be superfluous anyway. As an example, a Marriages table with Husband and Wife as two candidate keys (for the sake of this example assume the scope is active, monogamous, male-female marriages). The rules of the business domain demand that no-one should be simultaneously married to more than one person so both Husband and Wife keys are equally important. Why should it be necessary to choose just one of them as "primary" when there is no basis in reality for that primacy and no practical difference arising from such a choice?
Your example of marriages is flawed. It would be quite possible to have two couples where the husbands and/or the wives have the same names. If you make the Husband name or the Wife name a PK you would be unable to enter another couple in the table where the Husband name of Wife name would be duplicated.
December 4, 2013 at 11:00 am
Why would someone need a primary key for husband and wife?
None of the two makes a good primary key, but both should have a unique constraint - if such a table structure would be used in the first place (which I wouldn't recommend for several reasons being out of scope here).
Instead I would add an addtional Identity column being defined as both, the clustered index as well as the primary key.
For the husband and wife columns I would add a foreign key reference to the persons table. 😉
Your comment
the PRIMARY KEY constraint is essentially just syntactical sugar
makes me wondering what you think about the concepts to ensure referential integrity between data in different tables.
Is this just "nice to have" from your point of view? If so, all I can offer is to agree to disagree...
December 4, 2013 at 11:11 am
Lynn Pettis (12/4/2013)
Your example of marriages is flawed. It would be quite possible to have two couples where the husbands and/or the wives have the same names. If you make the Husband name or the Wife name a PK you would be unable to enter another couple in the table where the Husband name of Wife name would be duplicated.
I never suggested using names as keys. The hypothetical requirement is simply to identify people who are married and in the example the identifiers are the attributes called husband and wife. The example would be much the same whatever unique identifiers are used.
In practice it's extremely common to have more than one key for things. Historically (pre the relational model) there were strong reasons to designate one and only one key as "primary". Today there is very little less reason. Modern DBMSs ought to be capable of handling multiple keys without imposing design-time choices that may ultimately place limits on flexibility. For the most part DBMSs do succeed in doing that but there are a few instances where they don't and an over-reliance on PRIMARY KEY constraints is one symptom of that.
December 4, 2013 at 11:15 am
LutzM (12/4/2013)
Why would someone need a primary key for husband and wife? None of the two makes a good primary key, but both should have a unique constraint
QED. 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.
December 4, 2013 at 11:25 am
Just going to have to agree to disagree.
Yes, you can have three candidate keys that could be designated as a Primary Key. One should be selected as the Primary Key. The others can be designated as unique indexes with not null constraint. I don't see how this impacts flexibility in any way.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply