Results 1 to 5 of 5

Thread: CHECK constraint - referencing another column

  1. #1
    Chris Williams Guest

    CHECK constraint - referencing another column


    I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???

    Thanks in advance!

    Server: Msg 8141, Level 16, State 1, Line 1
    Column CHECK constraint for column 'end_date' references another column, table 'Session'.

    Here's an example of the script that I'm using:
    CREATE TABLE Session (
    session_key char(18) NOT NULL,
    course_key char(18) NOT NULL,
    site_key char(18) NOT NULL,
    instructor_key char(18) NOT NULL,
    start_date smalldatetime NULL,
    end_date smalldatetime NULL
    CHECK (end_date >= start_date)
    )


  2. #2
    Join Date
    Feb 2009
    Posts
    1
    I think there is a problem in syntax, you have missed a coma at the end of "end_date smalldatetime NULL" give a coma there and run your command .i am sure it will work.......

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  4. #4
    Join Date
    Jul 2009
    Posts
    2
    Quote Originally Posted by Chris Williams View Post
    I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???

    Thanks in advance!

    Server: Msg 8141, Level 16, State 1, Line 1
    Column CHECK constraint for column 'end_date' references another column, table 'Session'.

    Here's an example of the script that I'm using:
    CREATE TABLE Session (
    session_key char(18) NOT NULL,
    course_key char(18) NOT NULL,
    site_key char(18) NOT NULL,
    instructor_key char(18) NOT NULL,
    start_date smalldatetime NULL,
    end_date smalldatetime NULL
    CHECK (end_date >= start_date)
    )
    I think this code that is repaired :
    There are 2 ways :
    1. Make constraint "CHECK" in lavel table
    CREATE TABLE Session (
    session_key char(18) NOT NULL,
    course_key char(18) NOT NULL,
    site_key char(18) NOT NULL,
    instructor_key char(18) NOT NULL,
    start_date smalldatetime NULL,
    end_date smalldatetime NULL,
    CONSTRAINT date_ck CHECK (end_date >= start_date)
    )

    2. After create table Session
    alter table Session
    add constraint date_ck CHECK (end_date >= start_date)
    )

    Good day

  5. #5
    Join Date
    Jul 2009
    Posts
    2
    There are 2 ways for making constraint referencing another column.
    1. Making constraint in table level :

    CREATE TABLE Session (
    session_key char(18) NOT NULL,
    course_key char(18) NOT NULL,
    site_key char(18) NOT NULL,
    instructor_key char(18) NOT NULL,
    start_date smalldatetime NULL,
    end_date smalldatetime NULL,
    CONSTRAINT date_ck CHECK (end_date >= start_date)
    )

    2. After creating table "Session"
    alter table Session
    add constraint date_chk CHECK (end_date >= start_date)

    Funny with good day

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •