Two tables with the same primary key ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Two tables with the same primary key ?

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
DJB
Forum Newbie
Posts: 21
Joined: Tue Mar 15, 2005 6:15 am

Is that the way it works ?

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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 :)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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 :)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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)
Post Reply