CODEWITHSUNDEEP
sqlsql-server
BueKoW
BueKoW

Reputation: 966

SQL Check Constraint for Multiple Columns

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!!

Upvotes: 3

Views: 2890

Answers (1)

A-K
A-K

Reputation: 17090

You need a UNIQUE constraint:

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

Upvotes: 7

Related Questions