Conditional null constraint on Null

Question

I have the following SQL table:

create table dbo.[File]
(
  Id int identity not null
    constraint PK_File_Id primary key clustered (Id),
  MimeType nvarchar (400) not null,
  Name nvarchar (80) null,
  Slug nvarchar (80) null
)

How can I create a constraint that allows Name and Slug to be both null but never only one of them. So when Name or Slug is not null the other must also be not null.

basically set a constraint on `(name is null) xor (slug is null)`: http://stackoverflow.com/questions/5411619/t-sql-xor-operator — Marc B, Jun 08 '15 at 14:30
Are you sure MS SQL Server supports xor? I tried and does not seem to support it. — Miguel Moura, Jun 08 '15 at 14:42

GarethD · Accepted Answer · 2015-06-08T14:47:54.853

Your check constraint would be like this:

ALTER TABLE dbo.[File] 
    ADD CONSTRAINT CHK_File__Name_Slug 
    CHECK ((Slug IS NULL AND Name IS NULL) OR (Name IS NOT NULL AND Slug IS NOT NULL));

-- TESTS
INSERT dbo.[File] (MimeType, Name, Slug) VALUES ('X', NULL, 'X'); -- FAIL 
INSERT dbo.[File] (MimeType, Name, Slug) VALUES ('X', 'X', 'X');
INSERT dbo.[File] (MimeType, Name, Slug) VALUES ('X', 'X', NULL); -- FAIL 
INSERT dbo.[File] (MimeType, Name, Slug) VALUES ('X', NULL, NULL);

EDIT

(My comment was too long...)

I don't think there is a shorter way, and even if there were it would only be syntactic sugar, logically the same check would be performed.

If the real example is a lot more columns and you are really worried about the cost of checking all the columns, then create an extension table, something like:

CREATE TABLE dbo.[FileExendedProperties] 
(
        FileID INT NOT NULL,
        Name NVARCHAR (80) NOT NULL,
        Slug NVARCHAR (80) NOT NULL,
    CONSTRAINT PK_FileExendedProperties__FileID PRIMARY KEY (FileID),
    CONSTRAINT FK_FileExendedProperties__FileID FOREIGN KEY (FileID) 
        REFERENCES dbo.[File] (FileID)
);

Since the columns in your extension table are not nullable, they must either all be not null, or there not be a record in there at all, e.g. all columns will be null.

Yes, that was what I ended up using ... I wasn't sure if there was a shortest option. — Miguel Moura, Jun 08 '15 at 14:37

score 0 · Answer 2 · answered Jun 08 '15 at 14:30

0

You need to take a look at check constraints. Please see documentation below

https://msdn.microsoft.com/en-US/library/ms187550.aspx

answered Jun 08 '15 at 14:30

Alec.

5,371
5
34
69

Yes, I was doing a check constraint ... But how to do that with a check constaint? Usually I use it to restrict a column to certain values and not for null with another column – Miguel Moura Jun 08 '15 at 14:32

Conditional null constraint on Null

2 Answers2