Acceptable way to get around a UNIQUE w NULL constraint?

Question

I have a table as follows:

I would like to add a UNIQUE KEY on (connection_id, parent_container_id, name). However, parent_container_id and connection_id are a disjoin union -- the record must have one or the other. Because of this, I thought perhaps using the value 1 as the 'null' value (that is, the first entry when using an auto-incrementing ID) and creating a BASE entry in the container table for the 1 record.

Example data that I want to enforce uniqueness on:

• (connection_id=1, parent_container=null, name="hello")

• (connection_id=1, parent_container=null, name="hello") # should fail

• (connection_id=null, parent_container=10, name="goodbye")

• (connection_id=null, parent_container=10, name="goodbye") # should fail

Is this a poor approach to use or does this seem like a good way to enforce the Unique-ness constraint here? If it's not a good way, what might be a better way?

---

Update: my current solution is now using a generated (virtual) column with an md5 hash:

ALTER TABLE container ADD unique_hash2 CHAR(32) GENERATED ALWAYS AS 
(MD5(CONCAT(COALESCE(connection_id, '-1'), COALESCE(parent_container_id, '-1'), name))) VIRTUAL UNIQUE

Answer 1

Relational Solution

Responding to the relational-database tag, in that context. SQL is the data sublanguage defined by Codd in his Relational Model, that was established as a Standard in the 1980's. Thus SQL is Relational; Relational is SQL.

• All items in this answer are Relational and can be implemented easily in SQL, we have had them for decades.

---

Is this a poor approach to use or does this seem like a good way to enforce the Unique-ness constraint here? If it's not a good way, what might be a better way?

Yes, it is poor, on two counts. First, you are trying to do too many things in one file. Second, it is not Relational, and there is a clean; logical way to do that within the Relational paradigm: Therefore your intended addition is problematic.

• this is not a database design question, but strictly answering what is wrong, with an explanation.

1. Container
   container_id; name and parent_container_id indicate a simple, single-parent hierarchy. That is one Fact.

   • since you have more than one data tree or hierarchy, each of which has a Root, you need an Anchor row with container_id 0, that the roots are children of, and to inform the recursive Function that constructs the Path or PathName when to stop. This is an Anchor row, it means something, it is not a fiddle to avoid Null.
   • the AK ( parent_container_id, name ) is required to prevent duplicate names within a parent_container_id. That is one Predicate. Do not double up and try to do more with it, add another Predicate instead.

2. Connection
   connection_id is a separate discrete Fact. I presume it applies only to a Root. So this is a Subordinate Fact, which is optional to [1]. Its integrity is maintained by a constraint that calls a Function (same as in the Subtype doc) that checks the parent is a Root.

   • Null is a red flag indicating incomplete Normalisation, if you Normalise the data, you will not store a Null. (Nulls in result sets are allowed, of course.)

   • Separately, as a rule, since Null is the unknown, the notion of a Key (UNIQUE) that is unknown, is hysterical. That is why the Standard prohibits it.

3. "Union Type"
   In the Relational paradigm, the concept of "union type" is completely bankrupt. It is a method used by academics, who do not understand the Relational Model or SQL, who teach and promote 1960's Record Filing Systems and database systems that do not implement the SQL standard correctly. Since those database systems do not support SQL (Constraint that calls a Function), the method is an alternative that is possible: an additional index in the parent and an additional Foreign Key in the child. Horrendous at every level. Do not use it as a generic method.

   • note that the additional index is perverse in the index sense (container_id is already unique; adding something to an unique field does not make it more unique), its only purpose is to allow a Foreign Key in the child, that in turn is checked for some condition (here, that the parent is a Root).

   • obviously, if your "SQL" isn't SQL, use the pretend "SQL" method

4. Disjunction
   To implement an OR or XOR Gate (formal disjunction), use a proper Subtype cluster (instead of overloading the already poor file that you have).

Data Model

This model provides a relational-database solution, which implies SQL, and therefore proper Constraints that can call a Function.

• It is impaired because the "Primary Key" is not a Key but a physical RecordId, that does not have the properties of a logical Key. The consequence will be noticed in child tables.

This model provides a solution that is possible in MySQL.

• the typical 1960's pre-Relational, pre-DBMS, physical Record Filing System, that is marketed by the academics as "relational",

• with the additional index and FKs to substitute for SQL Constraints, for use in the databases that do not implement SQL correctly

Notation

• All my data models are rendered in IDEF1X, available from the early 1980's, the one and only Standard for Relational Data Modelling 1993.

• The IDEF1X Introduction is essential reading for those who are new to Codd's Relational Model, or its modelling method. Note that IDEF1X models are complete, they are rich in detail and precision, showing all required details, whereas a home-grown model, being unaware of the imperatives of the Standard, have far less definition. Which means, the notation needs to be fully understood.

Answer 2

Enforcing such requirements with:

• two unique indexes

• check constraint

Table definition:

CREATE TABLE tab(
   container_id          INT PRIMARY KEY AUTO_INCREMENT,
   connection_id         INT,
   parent_container_id   INT,
   name          VARCHAR(100),
   UNIQUE INDEX (connection_id, name),
   UNIQUE INDEX (parent_container_id, name),
   CHECK ((connection_id IS NULL AND parent_container_id IS NOT NULL)
           OR
          (connection_id IS NOT NULL AND parent_container_id IS NULL))
);

db<>fiddle demo

Data test:

INSERT INTO tab(connection_id, parent_container_id, name)
VALUES (1, NULL, 'hello');

INSERT INTO tab(connection_id, parent_container_id, name)
VALUES (1, NULL, 'hello');
-- Duplicate entry '1-hello' for key 'tab.connection_id'


INSERT INTO tab(connection_id, parent_container_id, name)
VALUES (NULL, 10, 'goodbay');

INSERT INTO tab(connection_id, parent_container_id, name) 
VALUES (NULL, 10, 'goodbay');
-- Duplicate entry '10-goodbay' for key 'tab.parent_container_id'


INSERT INTO tab(connection_id, parent_container_id, name)
VALUES (NULL, NULL, 'a');
-- Check constraint 'tab_chk_1' is violated.

INSERT INTO tab(connection_id, parent_container_id, name)
VALUES (2, 2, 'a');
-- Check constraint 'tab_chk_1' is violated.

Answer 3

Some things are best done in the application. Use it to do the enforcement. Or consider using Stored Procedure wrappers around the SQL code needed.

The one PRIMARY KEY for a table must include non-NULL column(s). But a UNIQUE secondary index may include NULL column(s).

Hence, neither of the two columns can be the PK. Nor can the combination of them.

Answer 4

The SQL92 standard defines:

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. In addition, if the unique constraint was defined with PRIMARY KEY, then it requires that none of the values in the specified column or columns be the null value.

For NULL, it can be understood as unknown. Although we don't know it now, it has many possibilities in the future, but we don't know it yet. Of course many people think it is a bug.

For the two fields parent_container and connection_id described above, why not from a business point of view, for NULL, set a value that is considered impossible in business instead, so as to avoid the limitation of MySQL unique key