Add a SQL Xor Constraint Between Two Nullable Fk'S

Add a SQL XOR Constraint between two nullable FK's

One way to achieve it is to simply write down what "exclusive OR" actually means:

CHECK (
    (FK1 IS NOT NULL AND FK2 IS NULL)
    OR (FK1 IS NULL AND FK2 IS NOT NULL)
)

However, if you have many FKs, the above method can quickly become unwieldy, in which case you can do something like this:

CHECK (
    1 = (
        (CASE WHEN FK1 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK2 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK3 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK4 IS NULL THEN 0 ELSE 1 END)
        ...
    )
)

BTW, there are legitimate uses for that pattern, for example this one (albeit not applicable to MS SQL Server due to the lack of deferred constraints). Whether it is legitimate in your particular case, I can't judge based on the information you provided so far.

Postgres SQL Exclusive OR (XOR) CHECK CONSTRAINT, is it possible?

You can't compare NULL values with =, you need IS NULL

(a IS NOT NULL AND b is NULL) OR (b IS NOT NULL AND a is NULL)

For a check constraint you need to enclose the whole expression in parentheses:

create table xor_test 
(
  id integer primary key, 
  a integer, 
  b integer, 
  check ((a IS NOT NULL AND b is NULL) OR (b IS NOT NULL AND a is NULL))
);

-- works
INSERT INTO xor_test(id, a, b) VALUES (1, null, 1);

-- works
INSERT INTO xor_test(id, a, b) VALUES (2, 1, null);

-- fails
INSERT INTO xor_test(id, a, b) VALUES (3, 1, 1); 

Alternatively the check constraint can be simplified to

check ( num_nonnulls(a,b) = 1 )

That's also easier to adjust to more columns

Two foreign keys, one of them not NULL: How to solve this in SQL?

Your data model is fine. In most databases, you would also add a check constraint:

alter table `time` add constraint chk_time_project_special_work
    check (project is not null xor special_work is null);

However, MySQL does not support check constraints. You can implement the logic using a trigger, if you really like.

Best relation between one and three other tables

So relation is more or less a contact, which is either a private relation (a contact to a person) or to a company or to a particular person in a company.

So a relation should have two optional (i.e. nullable) fields: for a person and for a company.

• Person (id_person, firstname, surname, ...)
• Company (id_company, name, ...)
• CompanyPerson (id_person, id_company, job, salary, ...)
• Relation (id_relation, id_person, id_company, letter, ...)

You'd have foreign key constraints to Person, Company and CompanyPerson, so whenever id_person is filled it must be in table Person; whenever id_company is filled it must be in table Company; whenever both are filled they must be in table CompanyPerson.

Moreover you'd add a check constraint to ensure that at least one of the two fields is filled for each Relation record.

How to guarantee only a value exists between two nullable column in a table

You could use CHECK (MySQL 8.0.16+):

create table user(
  employeeId int null,
  teacherId  int null,
  foreign key (employeeId) references employee (id),
  foreign key (teacherId) references teacher (id),
  CHECK ((employeeId IS NULL) + (teacherId IS NULL) = 1)
);

db<>fiddle demo

---

CREATE TABLE employee(id INT PRIMARY KEY);
INSERT INTO employee VALUES(1);

CREATE TABLE teacher(id INT PRIMARY KEY);
INSERT INTO teacher VALUES(1);

create table user(
  employeeId int null,
  teacherId  int null,
  foreign key (employeeId) references employee (id),
  foreign key (teacherId) references teacher (id),
  CHECK ((employeeId IS NULL) + (teacherId IS NULL) = 1)
);

INSERT INTO user VALUES(NULL,NULL);
-- Check constraint 'user_chk_1' is violated.

INSERT INTO user VALUES(1,1);
-- Check constraint 'user_chk_1' is violated.

INSERT INTO user VALUES(1,NULL);
INSERT INTO user VALUES(NULL,1);

SELECT * FROM user;

How to create multiple one to one's

You are using the inheritance (also known in entity-relationship modeling as "subclass" or "category"). In general, there are 3 ways to represent it in the database:

1. "All classes in one table": Have just one table "covering" the parent and all child classes (i.e. with all parent and child columns), with a CHECK constraint to ensure the right subset of fields is non-NULL (i.e. two different children do not "mix").
2. "Concrete class per table": Have a different table for each child, but no parent table. This requires parent's relationships (in your case Inventory <- Storage) to be repeated in all children.
3. "Class per table": Having a parent table and a separate table for each child, which is what you are trying to do. This is cleanest, but can cost some performance (mostly when modifying data, not so much when querying because you can join directly from child and skip the parent).

I usually prefer the 3rd approach, but enforce both the presence and the exclusivity of a child at the application level. Enforcing both at the database level is a bit cumbersome, but can be done if the DBMS supports deferred constraints. For example:

CHECK (
    (
        (VAN_ID IS NOT NULL AND VAN_ID = STORAGE_ID)
        AND WAREHOUSE_ID IS NULL
    )
    OR (
        VAN_ID IS NULL
        AND (WAREHOUSE_ID IS NOT NULL AND WAREHOUSE_ID = STORAGE_ID)
    )
)

This will enforce both the exclusivity (due to the CHECK) and the presence (due to the combination of CHECK and FK1/FK2) of the child.

Unfortunately, MS SQL Server does not support deferred constraints, but you may be able to "hide" the whole operation behind stored procedures and forbid clients from modifying the tables directly.

---

Just the exclusivity can be enforced without deferred constraints:

The STORAGE_TYPE is a type discriminator, usually an integer to save space (in the example above, 0 and 1 are "known" to your application and interpreted accordingly).

The VAN.STORAGE_TYPE and WAREHOUSE.STORAGE_TYPE can be computed (aka. "calculated") columns to save storage and avoid the need for the CHECKs.

--- EDIT ---

Computed columns would work under SQL Server like this:

CREATE TABLE STORAGE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE tinyint NOT NULL,
    UNIQUE (STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE VAN (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(0 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE WAREHOUSE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(1 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

-- We can make a new van.
INSERT INTO STORAGE VALUES (100, 0);
INSERT INTO VAN VALUES (100);

-- But we cannot make it a warehouse too.
INSERT INTO WAREHOUSE VALUES (100);
-- Msg 547, Level 16, State 0, Line 24
-- The INSERT statement conflicted with the FOREIGN KEY constraint "FK__WAREHOUSE__695C9DA1". The conflict occurred in database "master", table "dbo.STORAGE".

Unfortunately, SQL Server requires for a computed column which is used in a foreign key to be PERSISTED. Other databases may not have this limitation (e.g. Oracle's virtual columns), which can save some storage space.

Is this a bug in MERGE, failing to implement FOREIGN KEY properly?

Looks like a definite bug in MERGE to me.

The execution plan has the Clustered Index Merge operator and is supposed to output [Cars].ID,[Cars].Type for validation against the Vehicles table.

Experimentation shows that instead of passing the value "Car" as the Type value it is passing an empty string. This can be seen by removing the check constraint on Vehicles then inserting

INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');

The following statement now works

MERGE dbo.Cars AS TargetTable
    USING 
        ( SELECT    3 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED 
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

But the end result is that it inserts a row violating the FK constraint.

Cars

ID          Type  OtherData
----------- ----- ----------
3           Car   Some Data

Vehicles

ID          Type
----------- -----
1           Car
2           Truck
3           

Checking the constraints immediately afterwards

DBCC CHECKCONSTRAINTS  ('dbo.Cars')

Shows the offending row

Table         Constraint          Where
------------- ------------------- ------------------------------
[dbo].[Cars]  [Cars_FK_Vehicles]  [ID] = '3' AND [Type] = 'Car'

Adding a XOR constraint via Laravel migration

I think there are 2 good solutions

1. Seperate the data in 2 tables

Table1 -> data, rules, constraint (FK)

Table2-> id (PK, referenced by Table1), content (allow/restrict numbers), isAllow(bool)

That way you do the constraint in the database. This is the better solution, because now you don't have null values in your database. Normalisation is a good thing.

2. Use the event listener to check before insert

https://laravel.com/docs/5.6/eloquent#events

public function creating(Table1 $t1)
{
       // check if 1 is null and 1 is not null before creating
}

---

Related Topics