SQL Validation Limit for Multiple Columns

I am new to SQL CHECK CONSTRAINTand you need to check something that the combination of the three columns in my table does not match that on the other row.

I have a report table that includes three columns that I need to check: NAME, CREATEDBY, and TYPE. No multiple lines with these three values ​​can be the same.

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 restriction currently makes no sense, since it does not provide a logical ...

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

Thanks in advance!

+3
source share
1 answer

You need a UNIQUE constraint:

CONSTRAINT UNQ_Name_CreatedBy_Type UNIQUE ([Name], CreatedBy, [Type])
+7
source

All Articles