June 16, 2009 at 6:43 am
Hi all,
I have a question regarding primary key, why we can't have more than 1 primary key in a table?
---------------------------------------------------
The Greatest pleasure in life is doing what people say you can't do! 🙂
MS-ACCESS DBA!! :hehe:
June 16, 2009 at 7:13 am
hi there,
I would love to know why do you need two primary keys in your table.
Although i think in relational database a primary key is set to uniquely access/create rows in table. So that duplicate tuples(rows)
cannot be entered and according to relational rules (by that i mean codd rules)
one row should uniquely identified in only one way.
Apart from that primary key create clustered index on table which can only be one.
Hence considering the above two points I guess primary key is kept only one for one table.
Please correct me if I am wrong.
June 16, 2009 at 7:22 am
t.hitendra (6/16/2009)
hi there,I would love to know why do you need two primary keys in your table.
Although i think in relational database a primary key is set to uniquely access/create rows in table. So that duplicate tuples(rows)
cannot be entered and according to relational rules (by that i mean codd rules)
one row should uniquely identified in only one way.
Apart from that primary key create clustered index on table which can only be one.
Hence considering the above two points I guess primary key is kept only one for one table.
Please correct me if I am wrong.
Primary key doesn't have to be clustered. You can create a primary key which is not clustered. Primary key should identify each row in a table and can not have null value. Since there is no need to have more then one way to identify the row, there is no need for more then one primary key. You can how ever have primary key and unique constraints on other columns at the same table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 16, 2009 at 7:39 am
Hi Adi,
Thanks for correction I wasn't sure about clustered index on primary keys though.
May be you can point what anomalies could arise if however (not out of need) more than one primary keys are allowed on one table.
Thanks in Advance.
June 16, 2009 at 7:39 am
What feature(s) of the primary key would you like to have multiples of in the table?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 16, 2009 at 7:56 am
t.hitendra (6/16/2009)
Hi Adi,Thanks for correction I wasn't sure about clustered index on primary keys though.
May be you can point what anomalies could arise if however (not out of need) more than one primary keys are allowed on one table.
Thanks in Advance.
Since the role of primary key is to uniquely identify any record in the table, if we'll have 2 primary keys, we'll have 2 ways of uniquely identify the records. Suppose that I have a table with 2 primary keys. If I ask you to update the record where the primary key equals to 10, you'll might find 2 records with this primary key (each primary key looks at different column). There is no problem of having few unique constraints in the table, but primary key should be only one.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 16, 2009 at 8:16 am
As stated earlier, you can only have one primary key. This key uniquely identifies a record in a table. It is possible to have other keys on other columns that could be used to uniquely identify rows as well. These are called candadate keys (or alternate keys, by some). In SQL Server they would be defined as unique keys.
June 16, 2009 at 11:11 pm
thanks all for your replies,i know all the above rules and constraint about the relational database, my intention was to know the reason behind this rules. BTW thanks once again.:-)
---------------------------------------------------
The Greatest pleasure in life is doing what people say you can't do! 🙂
MS-ACCESS DBA!! :hehe:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply