October 15, 2018 at 7:02 pm
I am adding a check constraint to the BuildingNumber column in a table called Departments as a table level constraint.
I have a copy of the Departments table called Departments2. In this table, I am adding a check constraint to the BuildingNumber column as a column level constraint, but it is not working.
1. What am I doing wrong?
2. When you want to add a constraint to a column, how do you know if you should add it as a column level constraint or as a table level constraint?
Here is my code:
ALTER
TABLE Departments
ADD CONSTRAINT CK_BuildingNumber CHECK (BuildingNumber >=1 AND BuildingNumber <=5);
ALTER TABLE Departments2
ALTER COLUMN BuildingNumber smallint CONSTRAINT CK_BuildingNumber2 CHECK (BuildingNumber >= 1 AND BuildingNumber <=5);
October 15, 2018 at 8:46 pm
michael.leach2015 - Monday, October 15, 2018 7:02 PMI am adding a check constraint to the BuildingNumber column in a table called Departments as a table level constraint.
I have a copy of the Departments table called Departments2. In this table, I am adding a check constraint to the BuildingNumber column as a column level constraint, but it is not working.
1. What am I doing wrong?
2. When you want to add a constraint to a column, how do you know if you should add it as a column level constraint or as a table level constraint?Here is my code:
ALTER
TABLE DepartmentsADD CONSTRAINT CK_BuildingNumber CHECK (BuildingNumber >=1 AND BuildingNumber <=5);
ALTER TABLE Departments2
ALTER COLUMN BuildingNumber smallint CONSTRAINT CK_BuildingNumber2 CHECK (BuildingNumber >= 1 AND BuildingNumber <=5);
I modified my column level constraint as follows and I get the error "incorrect syntax near the keyword CHECK" so I still don't know what is wrong.
ALTER TABLE Departments2
ALTER COLUMN BuildingNumber smallint CHECK (BuildingNumber >= 1 AND BuildingNumber <=5);
October 16, 2018 at 5:22 am
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017
You will note that ALTER TABLE ... ALTER COLUMN don't allow for the addition of a check constraint. You want ALTER TABLE ... ADD CONSTRAINT, or, if you're adding a new column, you can add the constraint definition with the column with ALTER TABLE ADD <column name> .... CHECK ...
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
October 16, 2018 at 7:53 pm
GilaMonster - Tuesday, October 16, 2018 5:22 AMhttps://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017You will note that ALTER TABLE ... ALTER COLUMN don't allow for the addition of a check constraint. You want ALTER TABLE ... ADD CONSTRAINT, or, if you're adding a new column, you can add the constraint definition with the column with ALTER TABLE ADD <column name> .... CHECK ...
You mentioned "ALTER COLUMN don't allow for the addition of a check constraint." After reviewing the link you attached a few times, if I am reading this correctly, it looks like ALTER COLUMN will not allow you to add any type of constraint at all (Primary key, Foreign key, Unique, Default and Check), except for NULL and NOT NULL.
Is this correct?
October 17, 2018 at 2:11 am
You were trying to add a check constraint, therefore I mentioned check constraint in my reply, not because only check constraints are invalid.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply