Make sure auto incremental ID is not in another table

Question

Okay for various reasons I need to make sure an auto incremental ID is not in another table when it is inserted. So the ID in table1 cannot be the same as the ID in table2. The ID in both tables must be auto incremented but the numbers cannot be the same as ID from both are inserted into another table with a trigger. I cannot have the auto incremental value set higher on one table than the other as that causes other problems.

My idea is to have a WHERE clause with a sub query in the auto increment part of the schema but I am not sure if that's possible.

Any ideas?

there are many similar questions here.. like [this one](http://stackoverflow.com/q/3030254/944681) — Michal Klouda, Sep 04 '12 at 20:53
@MichalKlouda Seems simple and effective. But being new to this, what's a stub? — Jacob Windsor, Sep 04 '12 at 21:07
are you new to google as well? :) check this [wikipedia article](http://en.wikipedia.org/wiki/Method_stub) — Michal Klouda, Sep 05 '12 at 06:58

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

I would recommend using a trigger to check whether the next ID exists using BEFORE INSERT.

This resource will show you how to fetch the next AUTO_INCREMENT value and this resource outlines how to create a BEFORE INSERT trigger. Hopefully these resources can get you on the right path.

edited May 23 '17 at 12:20

Community

1
1

answered Sep 04 '12 at 20:56

Kermit

33,827
13
85
121

Make sure auto incremental ID is not in another table

1 Answers1