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

A-K · Accepted Answer · 2011-03-21 14:45:28Z

7

You need a UNIQUE constraint:

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

answered Mar 21, 2011 at 14:45

A-K

17.1k8 gold badges58 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BueKoW Over a year ago

Ah-hah! That makes a lot more sense. Thanks for the quick response!

Collectives™ on Stack Overflow

SQL Check Constraint for Multiple Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related