mysql - unique index with nulls - actual solution anyone?

Question

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.

Answer 1

[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.

ALTER TABLE b2c_constant_bindings
ADD unique_md5 char(32) AS 
    (MD5(CONCAT_WS('X', ifnull(constant_id, 0), ifnull(company_id, 0), ifnull(object_id, 0), ifnull(property_id,0)))) 
    UNIQUE;

Instead of using CONCAT, I am using CONCAT_WS to avoid having same results in cases similar to CONCAT(1, 23) and CONCAT(12, 3) (ss per @Rick James' notice).