SQL Check Constraint for Multiple Columns

Question

I am new to the SQL CHECK CONSTRAINT and need something to verify that a combination of three columns in my table does not match those on another row.

I have a Report table including three columns that I need to check against: NAME, CREATEDBY, and TYPE. No multiples of a row with those three values being identical may be created.

Please help!

CREATE TABLE Report(
    ReportID    INT             IDENTITY(1,1) NOT NULL,
    [Name]      VARCHAR(255)    NOT NULL,
    CreatedBy   VARCHAR(50)     NOT NULL,
    [Type]      VARCHAR(50)     NOT NULL,
    PageSize    INT             NOT NULL DEFAULT 25,
    Criteria    XML             NOT NULL
    CONSTRAINT CHK_Name_CreatedBy_Type CHECK ([Name], CreatedBy, [Type])
)

ALTER TABLE Report
    ADD CONSTRAINT PK_Report PRIMARY KEY (ReportID)

Obviously, the constraint currently makes no sense as it does not provide a boolean...

CONSTRAINT CHK_Name_CreatedBy_Type CHECK ([Name], CreatedBy, [Type])

Thanks in advance!!

Answer 1

You need a UNIQUE constraint:

CONSTRAINT UNQ_Name_CreatedBy_Type UNIQUE ([Name], CreatedBy, [Type])