CODEWITHSUNDEEP
mysqlsql
wlraider70
wlraider70

Reputation: 186

creating a new table with a foreign key auto increment?

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)

Upvotes: 0

Views: 130

Answers (2)

wlraider70
wlraider70

Reputation: 186

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;

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

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());

Upvotes: 1

Related Questions