how can i set up a uniqueness constraint in mysql for columns that can be null?

Question

I know that in MySQL, UNIQUE constraits don't treat NULL values as equal. So if I have a unique constraint on ColumnX, then two separate rows can have values of NULL for ColumnX and this wouldn't violate the constraint. How can I work around this? I can't just set the value to an arbitrary constant that I can flag, because ColumnX in my case is actually a foreign key to another table. What are my options here?

Please note that this table also has an "id" column that is its primary key. Since I'm using Ruby on Rails, it's important to keep this id column as the primary key.

Note 2: In reality, my unique key encompasses many columns, and some of them have to be null, because they are foreign keys, and only one of them should be non-null. What I'm actually trying to do is to "simulate" a polymorphic relationship in a way that keep referential integrity in the db, but using the technique outlined in the first option in the accepted solution for question asked here: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association

Answer 1

If only one of the foreign keys can be non-null, then maybe it would be better to restructure your table. Instead of having colA | colB | colC with one FK and two NULLs, have instead fkType | fkId.

fkType would be an enum with the options, A,B,C corresponding to your foreign tables. fkId would be the ID in the foreign table. Now you can set a uniqueness constraint on (fkType,fkId).

So previously you might have had:

1    | NULL | NULL
NULL | 3    | NULL

Now you would have:

A | 1
B | 3