CREATE TABLE IF NOT EXISTS b2c_constants (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
is_deleted BOOL DEFAULT FALSE,
UNIQUE (name)
) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci
CREATE TABLE IF NOT EXISTS b2c_constant_bindings (
constant_id INT UNSIGNED NOT NULL,
company_id INT UNSIGNED NOT NULL,
object_id INT UNSIGNED DEFAULT NULL,
property_id INT UNSIGNED DEFAULT NULL,
value VARCHAR(255) NOT NULL,
UNIQUE (constant_id, company_id, object_id, property_id),
FOREIGN KEY (constant_id) REFERENCES b2c_constants (id) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (company_id) REFERENCES companies (id) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (object_id) REFERENCES b2b_objects (id) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (property_id) REFERENCES b2b_properties (id) ON UPDATE RESTRICT ON DELETE CASCADE
) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci
The problem is with the unique key in the bindings
table. If I have data like:
constant_id company_id object_id property_id value
1 1 null null foo
1 1 1 null bar
1 1 1 1 baz
You can duplicate the first two rows without any errors infinitely, which is obviously not desirable.
The idea with this structure is to allow to bind constants globally per company, per object in company, and per property in company, but allow only unique constants for each of those.
Is there a way to solve this in the database while keeping the foreign keys and a simple table structure? I know that I could do type ENUM ('company', 'object', 'property'), type_id INT
, but with that I lose the foreign keys as well as the required company ID for all constants.
Best Answer
[This is not tested as it requires MySQL 5.7.6 or above]
While I agree with the comments above, I still have an idea that can be tried, which I think is not the best in terms of performance, but it solves the issue you're describing.
The idea is to add a key that deals with
NULL
as a concrete value, like '0', or any other value. Then, uniquely index the combination of the fields that you want to be unique.MySQL 5.7.6 supports generated columns.
Instead of using
CONCAT
, I am usingCONCAT_WS
to avoid having same results in cases similar toCONCAT(1, 23)
andCONCAT(12, 3)
(ss per @Rick James' notice).