I have an entity that has multiple attributes, and these attributes are themselves entities.
No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. But at the same time, not all attributes are required.
The following DML illustrates this where attr_a
and attr_b
are not optional (do not allow NULL) whilst attr_c
and attr_d
are optional (do allow NULL):
CREATE TABLE attr_a (
attr_a_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_b (
attr_b_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_c (
attr_c_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_d (
attr_d_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE entity (
entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
attr_a_id INT(10) UNSIGNED NOT NULL,
attr_b_id INT(10) UNSIGNED NOT NULL,
attr_c_id INT(10) UNSIGNED NULL,
attr_d_id INT(10) UNSIGNED NULL,
UNIQUE KEY uq_attr (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
KEY `fk_attr_a_id` (attr_a_id),
KEY `fk_attr_b_id` (attr_b_id),
KEY `fk_attr_c_id` (attr_c_id),
KEY `fk_attr_d_id` (attr_d_id),
CONSTRAINT `fk_attr_a_id` FOREIGN KEY (attr_a_id) REFERENCES `attr_a` (attr_a_id),
CONSTRAINT `fk_attr_b_id` FOREIGN KEY (attr_b_id) REFERENCES `attr_b` (attr_b_id),
CONSTRAINT `fk_attr_c_id` FOREIGN KEY (attr_c_id) REFERENCES `attr_c` (attr_c_id),
CONSTRAINT `fk_attr_d_id` FOREIGN KEY (attr_d_id) REFERENCES `attr_d` (attr_d_id)
) ENGINE InnoDB;
So the intention is that these two rows from the entity
table should not be allowed:
1,10,20,30,NULL
2,10,20,30,NULL
The following pairs of rows should be allowed, as though nulls were actually values (I understand the BDB engine would allow this, but I will be using InnoDB.)
1,10,20,30,NULL
2,10,20,NULL,NULL
1,10,20,30,40
2,10,20,NULL,NULL
1,10,20,30,NULL
2,10,20,NULL,40
The tables described above is obviously flawed, because of how NULLs are handled with a uniqueness constraint -- it would allow those two rows to exist.
In the case of an entity
, attr_c
and attr_d
are optional rather than unknown. So I think allowing NULLs is not technically correct because the value is known to be undefined, rather than unknown.
I think the only practical solution is to reserve a item in each of the attribute tables for "undefined" (possibly with ID 0.)
I know this solution would work, but I want to avoid the mistake of adopting a known anti-pattern.