Two tables with the same primary key ?
Moderator: General Moderators
Two tables with the same primary key ?
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.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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.
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 ?
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.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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.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.
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.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.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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.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.
thanks for you definitions nielsene
Definitely the latter. The DBMS maintains an index for each primary key.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.
(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)