Mysql – Can you enforce uniqueness in MySQL based on column in foreign table

foreign keyMySQLunique-constraint

I am refactoring one of my database tables and I've come up with an issue.

Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness constraint on the combination of these three columns. Column my.d is a foreign key to an auto-incremented ID column in another table foreign.

I realized that this other table also has a column foreign.a and both column as are complete duplicates, i.e., if you join the tables together on d, my.a will always equal foreign.a. Apparently this is part of the requirements.

I would like to remove column my.a from my table, but that will remove the uniqueness constraint. Is there a way to somehow set up a uniqueness constraint on the combination of foreign.a, my.b, my.c? Or am I better off just leaving the duplicate column in place?

Best Answer

You cannot directly create a unique constraint across tables. I say directly because you could come up with some indirect scheme; what comes to my mind would be using triggers to insert into a third table that has the unique constraint. But that's a lot of extra work compared to the obvious solution, which is to keep the column "a" in the "my" table.

Related Solutions

Mysql – Combining Tables in MySQL

Yes, it is possible to do. There is a CREATE TABLE ... SELECT clause in MySQL that is designed specifically for that. I recommend you start with a simple SELECT statement that clearly defines what the new table would contain. E.g. if you have a mammals table:

CREATE TABLE mammals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO mammals (name) VALUES
    ('dog'),('cat'),('whale');

and a birds table

CREATE TABLE birds (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO birds (name) VALUES
    ('chicken'),('duck');

You can build this query:

SELECT name FROM mammals
UNION ALL 
SELECT name FROM birds;

The result will contain all animals. However, you may need to keep the animal type in the new table. Modify the query like this:

SELECT name, 1 as type FROM mammals
UNION ALL 
SELECT name, 2 as type FROM birds;

and create a new table

CREATE TABLE animaltype (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     type CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animaltype (type) VALUES
    ('mammal'),('bird');

Now, when you are completely happy with the result execute:

CREATE TABLE animals AS
SELECT name, 1 as type FROM mammals
UNION ALL 
SELECT name, 2 as type FROM birds;

ALTER TABLE animals ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Optionally:

DROP TABLE mammals;
DROP TABLE birds;

You can also create a foreign key for the animaltype.type column

Mysql – Fastest way to alter column type in a 2-billion+ records innodb table

Take a look here at Percona's online schema change tool (anything from Percona is good - I'd advise you to look at all of the tools while you're at it). You could also try Shlomo Noach's online table change tool - test with both and see which one works for you (again, look at his entire toolkit).

Best Answer

Related Solutions

Mysql – Combining Tables in MySQL

Mysql – Fastest way to alter column type in a 2-billion+ records innodb table

Related Question