mysql同一表设置两个唯一,如何在MySQL中的两个或多个表中具有唯一ID？

I have a table called events where all new information goes. This table works as a reference for all queries for news feed(s) so event items are selected from there and information corresponding to that event is retrieved from the correct tables.

Now, here's my problem. I have E_ID's in the events table which correspond to the ID of an event in a different table, be it T_ID for tracks, S_ID for status and so on... These ID's could be the same so for the time being I just used a different auto_increment value for each table so status started on 500 tracks on 0 etc. Obviously, I don't want to do that as I have no idea yet of which table is going to have the most data in it. I would assume status would quickly exceed tracks.

The information is inserted into the event table with triggers. Here's an example of one;

BEGIN

INSERT INTO events (action, E_ID, ID)

VALUES ('has some news.', NEW.S_ID, NEW.ID);

END

That ones for he status table.

Is there an addition to that trigger I can make to ensure the NEW.S_ID != an E_ID currently in events and if it does change the S_ID accordingly.

Alternatively, is there some kind of key I can use to reference events when auto incrementing the S_ID so that the S_ID is not incremented to a value of E_ID.

Those are my thoughts, I think the latter solution would be better but I doubt it is possible or it is but would require another reference table and would be too complex.

解决方案

It's really uncommon to require a unique id across tables, but here's a solution that will do it.

/* Create a single table to store unique IDs */

CREATE TABLE object_ids (

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

object_type ENUM('event', ...) NOT NULL

) ENGINE=InnoDB;

/* Independent object tables do not auto-increment, and have a FK to the object_ids table */

CREATE TABLE events (

id INT UNSIGNED NOT NULL PRIMARY KEY,

...

CONSTRAINT FOREIGN KEY (id) REFERENCES object_ids (id)

) ENGINE=InnoDB;

/* When creating a new record, first insert your object type into the object_ids table */

INSERT INTO object_ids(object_type) VALUES ('event');

/* Then, get the auto-increment id. */

SET @id = LAST_INSERT_ID();

/* And finally, create your object record. */

INSERT INTO events (id, ...) VALUES (@id, ...);

Obviously, you would duplicate the structure of the events table for your other tables.