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

  • Why not use NOT NULL DEFAULT ''?Mysql treats NULLs as different,even from each other, in an unique index – Mihai Nov 27 '16 at 21:39
  • agree, but because it INT - DEFAULT 0 will be better. 0 not allowed in parent table and could work as NULL in binding table fine. – a_vlad Nov 27 '16 at 21:43
  • FOREIGN KEYs do not fit all situations. – Rick James Nov 27 '16 at 22:51
  • 1
    You really ought to have an explicit PRIMARY KEY on every InnoDB table. – Rick James Nov 27 '16 at 22:51
  • 1
    @Mihai - NOT NULL DEFAULT anything does not work with foreign keys. – jurchiks Nov 27 '16 at 23:04
8

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

  • If you are going to use CHAR for hex characters, be sure to say CHARACTER SET ascii for the column. – Rick James Nov 28 '16 at 20:35
  • It will still produce the same hash for CONCAT_WS('X', 1, 2, NULL, 3) and CONCAT_WS('X', 1, 2, 3, NULL) but based on the sample data and structure we could assume that such data is not intended to appear (despite there is no constraint to enforce it) – ypercubeᵀᴹ Nov 28 '16 at 21:34
  • My main idea, that I skipped, is to use IFNULL function. I edited the question – Jehad Keriaki Nov 28 '16 at 22:20
  • I'll check this tomorrow, thanks! The performance in this case isn't that important as the inserts won't be very frequent. – jurchiks Nov 29 '16 at 3:17
  • Ok, so the task was delayed and I only got to checking this today, but there's a problem - the SQL statement you provided does not work for me: #1904 - Key/Index cannot be defined on a non-stored computed column. I am using MariaDB 10.1.18, and it is supposed to work for me: mariadb.com/kb/en/mariadb/virtual-computed-columns Supposedly it didn't work up until 10.1.6, but should work afterwards, with some limitations which don't affect me. The exact same error appears if I add the column without index and then the index separately, so the problem is in the UNIQUE index. – jurchiks Dec 5 '16 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.