Jump to content


Auto Incrementing Primary key field across multiple tables.

  • Please log in to reply
1 reply to this topic

#1 Cynicide

  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 05 July 2003 - 05:39 AM

Hi everyone,

I\'m designing a database for a news/reviews site and I have created tables for all the article types in the site.

Features - News - Previews - Reviews - Rants

I want all of these to share a primary key field that auto increments. I just can\'t find any place that documents how to get this working.

Would I have to make a new table called Index and put the primary key field in that and make all the other tables reference that one?

Any help or pointers to examples would be great!



#2 gizmola

  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 06 July 2003 - 04:34 AM

Seems like you are asking for a combination of declarative referential integrity and triggers for a subtyped table. There is no magical way of handling this type of design, especially with mysql. Mysql doesn\'t have triggers.

I assume that there must be some substantial commonality in the set of columns between the tables that makes this type of code worthwhile, because you are guaranteed to need a case statement to determine which subtype table you will join to.

The typical way of handling this is with 2 main tables, and 1..x subtables.

Table1 (Your main table... you called it index, should have an auto_increment key called indexid.

Table2 (The subtype table)
This has a subtypeid key and description. The rows in the table will be: 1. Features 2.News 3.Previews 4.Reviews 5.Rants

Tables 3-7 in your case are the feature, news, preview etc. tables.

You set Table 2 to have a foreign key relationship to Table1. That is, there should be a subtypeid column in your index table.

In each of your subtype tables, you will include the primary key from the index table in them. Here\'s an example:

CREATE TABLE features (
FOREIGN KEY (indexid) REFERENCES index (indexid),
PRIMARY KEY (indexid));

When you do an insert, you will insert your row into index first, setting the correct subtypeid for the subtable... ie 1 for an entry into the feature table.

Immediately following the successful insert into the index table you:

$indexid = mysql_insert_id($dbh); (where $dbh was the handle used in the insert query)

Then have your case statement, and you examine the value of $subtypeid, which tells you which child table you should be doing your follow up insert.

You then do an insert into that table, with the appropriate columns, and using the $indexid to set the indexid column value.

When querying the results out later, you join index to subtypetable by the indexid column.

Ideally you surround this process with a transaction, so that if at any point an insert fails, you can rollback the entire thing. The availabilty and syntax for a transaction-like capability really depends on the version of mysql you are using.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users