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

create a table with two primary keys. Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 10:59 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 12:21 PM
Points: 448, Visits: 3,382
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.



David
Post #1540916
Posted Wednesday, February 12, 2014 11:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 817, Visits: 2,059
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.



Post #1540921
Posted Wednesday, February 12, 2014 12:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, 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.....
Post #1540946
Posted Thursday, February 20, 2014 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:07 AM
Points: 140, Visits: 453
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
Post #1543377
Posted Thursday, February 20, 2014 3:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 40,159, Visits: 36,546
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 2008, MVP
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

Post #1543379
Posted Thursday, February 20, 2014 12:48 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 12:21 PM
Points: 448, Visits: 3,382
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.


David
Post #1543682
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse