Mysql constraint problem

database-designMySQL

I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this:

+------------+   +------------+    +------------+
| t1         |   | t2         |    | t3         |
+------------+   +------------+    +------------+
| t1_id (PK) |   | t2_id (PK) |    | t3_id (PK) |
+------------+   | some_value |    | t1_id (FK) |
                 +------------+    | t2_id (FK) |
                                   +------------+

PK = primary key, FK = foreign key, and all fields are of type INT

The problem I have is that I want to add a constraint to table t3 that involves the field t2.some_value. Basically I want the combination of t1.t1_id and t2.some_value to be unique within table t3. For example here is a situation I want to avoid:

  t1           t2                        t3
+-------+    +-------+------------+    +-------+-------+-------+
| t1_id |    | t2_id | some_value |    | t3_id | t1_id | t2_id |
+-------+    +-------+------------+    +-------+-------+-------+
| 10    |    | 20    | 1          |    | 30    | 10    | 20    | <-- t2.some_value = 1
| 11    |    | 21    | 2          |    | 31    | 10    | 21    |
+-------+    | 22    | 3          |    | 32    | 10    | 22    |
             | 23    | 1          |    | 33    | 10    | 23    | <-- t2.some_value = 1
             +-------+------------+    | 34    | 11    | 23    |
                                       +-------+-------+-------+

The problem here is that two records in t3 link the same t1 record (t1_id=10) to two different t2 records that have the same some_value. Is there any way to prevent this from happening? To my knowledge MySql does not allow constraints to be defined that span multiple tables.

I can think of one solution, but that involves duplicating the some_value field in table t3 so I can enforce a unique key directly on it. This obviously breaks the normalisation design rule, but is there any other way?

Best Answer

From what I can tell, you'll have to create the 'some_value' in table3. Here's a link to the manual page on foreign key constraints

There is an example with a 'complex' foreign key constraint that is similar to your setup, which duplicates the content in the third table. There is (minimal) comfort in knowing that hey, at least it's handled by a Foreign Key.

Related Solutions

Sql-server – Any way around unique index 16 column max

Add a persisted computed column that combines the 18 keys, then create an unique index on the computed column:

alter table t add all_keys as c1+c2+c3+...+c18 persisted;
create unique index i18 on t (all_keys);

See Creating Indexes on Computed Columns.

Another approach is to create an indexed view:

create view v 
with schemabinding
as select c1+c2+c3+...+c18 as all_keys
from dbo.t;

create unique clustered index c18 on v(all_keys);

See Creating Indexed Views.

Both approaches allow for a partial key aggregate: aggregate c1+c2+c3 as k1, c4+c5+c6 as k2 etc. then index/create indexed view on (k1, k2, ...). Thia could be beneficial for range scans (index can be used for search on c1+c2+c3.

Of course, all + operation in my example are string aggregation, the actual operator to use depends on the types of all those columns (ie. you may have to use explicit casts).

PS. As unique constraints are enforced by an unique index, any restriction on unique indexes will apply to unique constraints as well:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    constraint unq unique
      (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18));
go  


Msg 1904, Level 16, State 1, Line 3
The index '' on table 't' has 18 column names in index key list. 
The maximum limit for index or statistics key column list is 16.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, creating the constraint on a persisted computed column works:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    all_c as 
        c1+c2+c3+c4+c5+c6+c7+c8+c9+c10+c11+
        c12+c13+c14+c15+c16+c17+c18 
        persisted
        constraint unq unique (all_c));
go

Obviously, the persisted column consumes the space on disk so the approach may be bad for a very large table. The indexed view approach does not have this problem, it only consumes the space for the index, not the space for the computed column and index.

Proper database design for a drupal table with an order field

The problem is similar to this (SO) question:
How to swap values of two rows in mysql without violating unique constraint?

Unfortunately, the answer is the same. Due to how MySQL checks UNIQUE (and primary and foreign key) constraints, i.e. row by row and not at the end of statements or at the end of transactions, swapping or rearranging values that are part of unique constraints, cannot be done easily.

I would go with your 1st suggestion:

1. Update each row's order with some large number (i.e. 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).

This way you can have the minimal (2) statements required to do the update and any other connection that reads the table between the 2 statements will still read a consistent state of values.

Best Answer

Related Solutions

Sql-server – Any way around unique index 16 column max

Proper database design for a drupal table with an order field

Related Question