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.

Answer 1

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.

Answer 2

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

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