Page 1 of 1
Two tables with the same primary key ?
Posted: Fri Aug 19, 2005 12:12 pm
by DJB
Can you have two tables with the same primary key ? For example Table1 has module_code (text) as the primary key, as does table2. This does not seem to create any errors so how is the PK handled. Is an index created which is identified via table_name.primary_key ? If so it seems in conflict with Sql standards.
Posted: Fri Aug 19, 2005 12:17 pm
by nielsene
There's nothing wrong with that, why do you feel its in violation of the SQL standards?
Normally if you've done that its good to setup a foreign key link from one table to the other if one can be thought of as a "prime" tablee and the other is a subsidiary.
Posted: Fri Aug 19, 2005 12:21 pm
by raghavan20
as far as i know, its not so conventional to have two tables have the same pk. then they should more related together and we split it for the sake of understanding. sometimes, we feel a set of data related to some common noun i accessed rarely and you want to take that set of fields in a separate table.
pk itself is an index. apart from that, you can create as many indexes necessary on fields which would be used in search parameter. think there should not really be a problem with two tables having same pk.
Is that the way it works ?
Posted: Fri Aug 19, 2005 12:23 pm
by DJB
The key is unique to the table, and an index is built via table name & PK ? I thought that if you created a primary key it had to be unique within the database so that the requisite index could be created & scanned.
Posted: Fri Aug 19, 2005 12:25 pm
by raghavan20
to my knowledge, a pk is only for a table to uniquely identify the records in a table. there cannot be a pk to identify all records in the db.
you index is associated with the table
for ex: u use only show index from table_name;
its for searching to locate a record in a table if i am not wrong

Posted: Fri Aug 19, 2005 12:29 pm
by nielsene
raghavan20 wrote:
pk itself is an index. apart from that, you can create as many indexes necessary on fields which would be used in search parameter. think there should not really be a problem with two tables having same pk.
Incorrect. A Primary Key is an abstraction at the
logical level, while an index is at the
physical level. The logical level is concerned with "data modelling" and "knows" that a primary key is a unique identifier. The physical layer is all the implementation details; indexes live at this level as they are about mapping identifiers to bits on the disc. While most DBMS's automatically create an index for each primary key (and most use the index to enforce the uniqueness constraints), A Primary key is NOT an index.
Posted: Fri Aug 19, 2005 12:31 pm
by nielsene
raghavan20 wrote:to my knowledge, a pk is only for a table to uniquely identify the records in a table. there cannot be a pk to identify all records in the db.
That's correct. a Primary Key is unique to its table. Some databases, especiall OODBMS or ORDBMS create "Object IDs" which are unique across the entire database, but this is not a normal concept in RDBMS's.
Posted: Fri Aug 19, 2005 1:50 pm
by raghavan20
While most DBMS's automatically create an index for each primary key (and most use the index to enforce the uniqueness constraints), A Primary key is NOT an index.
when i was viewing this sql 'show index from table_name', it showed the primary key as an index so I assumed that every pk is optimised to be an index or an index is maintained on each primary key.
thanks for you definitions nielsene

Posted: Fri Aug 19, 2005 1:54 pm
by nielsene
raghavan20 wrote:
when i was viewing this sql 'show index from table_name', it showed the primary key as an index so I assumed that every pk is optimised to be an index or an index is maintained on each primary key.
Definitely the latter. The DBMS maintains an index for each primary key.
(This is one of my big pet peeves about MySQL -- they seem to confuse the issue more than most other DBMS. Using "KEY" instead of UNIQUE, equating "KEY" with "INDEX", showing the PK as an index, etc)