Can a MySQL InnoDB database have a foreign column uniqueness constraint?

Question

I am using a MySQL InnoDB database and have many tables in it. What I want to be able to do is enforce (from within the database) a constraint such that a key may exist in one of two columns (in two separate tables) but not both. I'll try to make this more clear.

Say I have two tables, TableA and TableB. Both of these tables have many columns, but they have one column in common, called SpecialID (int 255).

Now, both of these tables have many rows, and from the PHP side of the web app, the SpecialID column in TableA should never contain an integer that is in the SpecialID column of TableB, and the same goes the other way around. In other words, an integer should never be able to be found in the SpecialID column of TableA and TableB at any one time.

I'm fairly confident that I've enforced this from the PHP side, however I want to be able to enforce this relationship from within the database, just to be extra careful, as if I ever ended up with the same value in both tables, it would be catastrophic.

This may not even be possible, but I thought I'd throw it out there cos it seems like it could be. It would be sort of like a "foreign uniqueness constraint". I have done a bit of research but haven't turned up anything at all, not even people asking for something like this, so perhaps I could just be searching for the wrong thing?

Check this answer: http://stackoverflow.com/questions/408749/enforce-unique-values-across-two-tables — NewInTheBusiness, Jan 10 '14 at 04:04
Hmm, a lot of the answers there tend to suggest triggers. I've never used them before, but it looks like that _may_ work. One of the comments mentions something about GUIDs if you're dealing with integers. If this is possible, it may be tidier? Though I have no idea about them, either. — BSnapZ, Jan 10 '14 at 07:02

score 1 · Accepted Answer · answered Apr 30 '14 at 23:52

Here's a solution:

CREATE TABLE Specials (
    specialid INT AUTO_INCREMENT PRIMARY KEY, 
    type CHAR(1) NOT NULL, 
    UNIQUE KEY (id, type)
);

CREATE TABLE TableA (
    id INT AUTO_INCREMENT PRIMARY KEY,
    specialid INT NOT NULL,
    type CHAR(1) NOT NULL DEFAULT 'A',
    FOREIGN KEY (specialid, type) REFRENCES Specials(specialid, type)
);

CREATE TABLE TableB (
    id INT AUTO_INCREMENT PRIMARY KEY,
    specialid INT NOT NULL,
    type CHAR(1) NOT NULL DEFAULT 'B',
    FOREIGN KEY (specialid, type) REFRENCES Specials(specialid, type)
);

Now you need to make sure TableA.type is always 'A' and TableB.type is always 'B'. You can do this with a trigger, or else a foreign key to a lookup table of one row for each case.

The result is that Specials.type can be any letter, but only one letter for a given specialid. The rows in TableA and TableB can reference only a specialid with a type that matches their own type. This means that any given specialid can be referenced by only one table or the other, but never both.

score 0 · Answer 2 · edited May 23 '17 at 12:28

From what I've found, there is no easy way to do this. As I cannot find anything and no one has provided a solution to the issue, I will assume that it is in fact not possible (at least, not very easily).

For my own project, I ended up going along a different route to achieve my goal.

The discussion at this SO question may be of use for anyone searching for something along these lines: Enforce unique values across two tables I have not tried it myself, though.

If I ever come across anything better (or someone posts a better answer here) then I shall update my response and/or mark someone else's answer as correct as necessary.

Can a MySQL InnoDB database have a foreign column uniqueness constraint?

2 Answers2