ERROR Column CHECK constraint for column references another column

Question

I use MS SQL 2008 R2, I need create a Table with a CHECK on a specific column but I receive this error. Could you please point me out in the right direction? Thanks

HeatingSystem   tinyint             NOT NULL
    CONSTRAINT  CK_ReProperties_HeatingSystem   CHECK(Size between 0 and 3),

ERROR

Msg 8141, Level 16, State 0, Line 1 Column CHECK constraint for column 'HeatingSystem' references another column, table 'ReProperties'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

Answer 1

Constraints that are defined inline at column level can only reference the column they are defined next to.

Either move the constraint definition next to the correct column or move the constraint definition to the end of the table definition.

Fails

CREATE TABLE HeatingSystem
(
Size INT,
HeatingSystem TINYINT CHECK(Size between 0 and 3)
)

Succeeds

CREATE TABLE HeatingSystem
(
Size INT CHECK(Size between 0 and 3),
HeatingSystem TINYINT
)   

Also Succeeds

CREATE TABLE HeatingSystem
(
Size INT ,
HeatingSystem TINYINT,
CHECK(Size between 0 and 3 AND HeatingSystem BETWEEN 1 AND 10)
)  

The final way also allows you to declare a row level constraint referencing multiple columns.