SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create a table with two primary keys.


create a table with two primary keys.

Author
Message
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 3706
Luis Cazares (2/12/2014)
That's the difference of unique keys and primary keys. Primary keys will idenitfy a row within a table and they're essentially a preferred unique key. Unique keys will enforce uniqueness and a table can have as many as needed.

Any key identifies a row in a table. That's what we mean by a key. Actually any superkey identifies a row in a table. A key is just the minimal set of attributes in any superkey.

For me, the primary key on the marriages table should include both columns (husband & wife) or I might choose a surrogate key if the situation is correct for that.

But the requirement is that both Husband and Wife columns be unique (current, monogamous marriages only are permitted). The composite of Husband and Wife cannot be a primary key if Husband and Wife are both candidate keys because a key has to be irreducible - i.e. the smallest subset of attributes that form a superkey. It's true that in SQL Server you could create a PRIMARY KEY constraint on (Husband,Wife) while still having UNIQUE constraints on (Husband) and (Wife) individually but that's just a syntax peculiarity of SQL. The constraint named "PRIMARY KEY" in that case would not be a primary key constraint at all, it would just become a (mostly redundant) superkey constraint.

Adding a surrogate doesn't really alter things. That just adds a third key to the picture without answering the essential dilemma of whether and why one key needs to be preferred over any other.
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2702 Visits: 3495
No because I already stated that both Husband and Wife are keys (i.e. candidate keys). Enforcing all the desired keys of a table is of course extremely important


The second sentence above was not in your original post, and answers my comment. Thanks for the clarification.



sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2486 Visits: 487
In the world of BI it is very common in dimensional design to know both the physical primary key of a target dimension table and the logical equivalent business key. With dimensions being denormailzed quite often, there maybe be a primary key from more than one source table that contributes to a composite logical primary key in the target dimension--this is also known as the alternate key as Jeff has pointed out.

Hope it helps.....
azhar.iqbal499
azhar.iqbal499
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 525
Actually, when we define primary key on a table, data is stored in the physical order of primary key, We can't have two physical order in a table. This is the reason that we can't have two primary keys in a same table. Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86879 Visits: 45263
azhar.iqbal499 (2/20/2014)
Actually, when we define primary key on a table, data is stored in the physical order of primary key


Not true.

The clustered index (not primary key) enforces the logical storage order of the data. It does not enforce the physical storage order however.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 3706
azhar.iqbal499 (2/20/2014)
Actually, when we define primary key on a table, data is stored in the physical order of primary key, We can't have two physical order in a table. This is the reason that we can't have two primary keys in a same table. Thanks


Primary key has nothing to do with the physical order data is stored in. Keys are a logical construct not a physical one.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search