Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Restriction on Primary key Expand / Collapse
Author
Message
Posted Monday, December 2, 2013 11:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:00 AM
Points: 11, Visits: 63
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??
Post #1518964
Posted Monday, December 2, 2013 11:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 12,881, Visits: 31,815
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1518972
Posted Monday, December 2, 2013 12:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 7,019, Visits: 12,915
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1518984
Posted Monday, December 2, 2013 12:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 23,009, Visits: 31,509
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1518987
Posted Monday, December 2, 2013 12:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 7,019, Visits: 12,915
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,
[b] [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
(
[b] ASC
)





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1518989
Posted Monday, December 2, 2013 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 12,881, Visits: 31,815
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1518993
Posted Tuesday, December 3, 2013 3:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 15,517, Visits: 27,898
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1519144
Posted Wednesday, December 4, 2013 3:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 440, Visits: 3,268
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.


David
Post #1519547
Posted Wednesday, December 4, 2013 9:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 23,009, Visits: 31,509
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1519699
Posted Wednesday, December 4, 2013 10:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 440, Visits: 3,268
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?


David
Post #1519712
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse