creating a new table with a foreign key auto increment?

Question

I'm modifying an open-source program so I'm in a bit over my head. It's a system for tracking personal information built on mysql.

There is a table "person_per" in it is a column "per_ID. When a new person is added they get the next number.

I want a new table "follow_up". I need this table to have a "per_ID" column.(can I have a duplicate name if its on a different table?) So that any new info is still linked to the main record.

My biggest concern is that new records get a row on the "follow_up" table. It looks like slave / master might be the way to do that, but i have NO idea how to make that happen.

Im not sure if this is relevent....

mysql> SHOW INDEXES FROM person_per;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person_per |          0 | PRIMARY  |            1 | per_ID      | A         |         413 |     NULL | NULL   |      | BTREE      |         |               |
| person_per |          1 | per_ID   |            1 | per_ID      | A         |         413 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

score 1 · Answer 1 · answered Oct 05 '13 at 16:45

Yes, you can have the same column name in two different tables. You didn't really need to ask this as a question, it's quite easy to test for yourself:

CREATE TABLE test.t1 ( per_ID INT );
CREATE TABLE test.t2 ( per_ID INT );

You can create a table follow_up with a per_ID column. You can use a foreign key so that any value in that column must first exist in the referenced table person_per.

CREATE TABLE follow_up (
  /* perhaps other columns too */
  per_ID INT,
  FOREIGN KEY (per_ID) REFERENCES person_per (per_ID)
);

But this doesn't assure that a row exists in follow_up for every value of per_ID in the referenced table. It only prevents rows in follow_up from having a value that doesn't exist in the referenced table.

The easiest way to make sure a row is inserted into follow_up is to run an extra INSERT, and you can discover the per_ID generated by using the LAST_INSERT_ID() function:

INSERT INTO person_per ... /* whatever you normally insert */

INSERT INTO follow_up (per_ID) VALUES (LAST_INSERT_ID());

mysql> CREATE TABLE follow_up ( per_ID INT, FOREIGN KEY (per_ID) REFERENCES person_per (per_ID) ); ERROR 1005 (HY000): Can't create table 'cla-constituents.follow_up' (errno: 150) mysql> — wlraider70, Oct 11 '13 at 14:09
There's a good summary of troubleshooting errno 150 here: [MySQL Creating tables with Foreign Keys giving errno: 150](http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — Bill Karwin, Oct 11 '13 at 17:16

score 0 · Answer 2 · answered Oct 11 '13 at 14:48

0

For whatever reason an 'engine' statement was needed.

CREATE TABLE follow_up (
  per_ID INT,
  FOREIGN KEY (per_ID) REFERENCES person_per (per_ID)
)
engine=MyISAM;

answered Oct 11 '13 at 14:48

wlraider70

187
3
13

MyISAM does not support foreign keys, so it's no wonder this succeeded. It accepted and *ignored* your FK declaration. You'll find you now have no constraint against inserting data that violates referential integrity. – Bill Karwin Oct 11 '13 at 17:15

creating a new table with a foreign key auto increment?

2 Answers2