CODEWITHSUNDEEP
mysqlsqlforeign-keysrestriction
John Smith
John Smith

Reputation: 6197

MySQL, possible to prevent two fields to be NULL or NOT NULL?

a simple table:

ID, NAME, POST_ID, GROUP_ID

either POST_ID or GROUP_ID must be set, but never both of them, NEITHER none of them. So,

there are valid cases:

ID, NAME, POST_ID, GROUP_ID
x,   y,   1,       NULL
x,   y,   NULL,    4

and NOT VALID cases:

ID, NAME, POST_ID, GROUP_ID
x,   y,   NULL,    NULL
x,   y,   4,       4

is it possible to set such complicated restriction rule?

Upvotes: 2

Views: 575

Answers (2)

Francesco Simeoli
Francesco Simeoli

Reputation: 541

You have to use TRIGGERS on CREATE and UPDATE events and throw an exception when the condition (COALESCE(POST_ID, GROUP_ID) IS NULL OR (POST_ID IS NOT NULL AND GROUP_ID IS NOT NULL)) occurs

Here the answer to your question: Either OR non-null constraints in MySQL

This is the procedure, slightly change the syntax depending on the version of MySql.

Upvotes: 2

Erisan Olasheni
Erisan Olasheni

Reputation: 2905

You can use the CHECK constraint when creating your TABLE:

CREATE TABLE Persons (
    ID int NOT NULL,
    NAME varchar(255) NOT NULL,
    POST_ID int,
    GROUP_ID int,
    CHECK (POST_ID IS NULL XOR GROUP_ID IS NULL)
);

Upvotes: 2

Related Questions