Post reply

How do I add a table constraint that will only accept values Y or N

  • October 21, 2015 at 5:33 am

    #304437

    Team:

    Create table enrollment_in

    BEN_DENT VARCHAR(1)

    What I need is a constraint to limit the values to Y or N

  • October 21, 2015 at 5:36 am

    #1834589

    CREATE TABLE enrollment_in (

    BEN_DENT CHAR(1) NOT NULL CHECK (BEN_DENT IN ('Y', 'N'))

    );

    And change the data type to CHAR. There's no point in wasting 2 bytes of space on every row to specify how many characters are in a column that must always contain a single character

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2015 at 5:48 am

    #1834598

    Yes, or if the table already exists:ALTER TABLE enrollment_in WITH CHECK

    ADD CONSTRAINT CK_BEN_DENT CHECK (BEN_DENT IN ('Y', 'N'))

    The WITH CHECK option checks existing rows for compliance with the constraint. Not only does that check you don't have any bad data, it also makes the constraint trusted, meaning the query optimizer can use it to make better decisions on how to execute queries.

    John

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply