A check constraint referencing another column
-
The first block of code is from another website. Here the CHECK constraint is supposed to be applied to the MedicamentRegulated column (the only reason I know is because the person previously posted this as a column level constraint for the MedicamentRegulated column). The CHECK constraint references MedicamentRegulated and the DateValidity column.
1. How would SQL know that this table level constraint should be applied to the MedicamentRegulated column and not some other column?
2. Also, how is it that this person is able to reference another column (DateValidity) even though the constraint applies to the MedicamentRegulated column? The reason I ask is because In the second block of code (my code) I attempted to reference another column with a column level constraint and a table level constraint and for some reason I got an error stating:
"Column CHECK constraint for column 'Zip' references another column, table 'employeeMasterFile'."
CREATE TABLE dbo.Medicaments -- always use schema*( MedicamentID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(50), DateValidity DATE, MedicamentRegulated BIT, CONSTRAINT pk_Med PRIMARY KEY (MedicamentID), CONSTRAINT ck_Med_reg CHECK ( (MedicamentRegulated = 1 AND DateValidity IS NOT NULL) OR MedicamentRegulated = 0 ));Here is my code:
CREATE TABLE employeeMasterFile
(
Vendor varchar(30) NOT NULL,
Address varchar(30),
City varchar(20),
Email varchar(30),
State char(2),
Zip char(5)
CONSTRAINT CK_Zip CHECK(LEN(Zip)=LEN(State)+3)
)
-
You're getting confused with the 'table constraint' and column constraint'. Other than default and not null, constraints are objects applied to the table. There aren't two types of check constraints. There are just check constraints. There's shorthand syntax where you can define the constraint with the column or at the end of the column list, that's just syntax.
How SQL knows this involves the MedicamentRegulated column? Because the column is referenced in the constraint. It's not an attribute of the column though.
CONSTRAINT ck_Med_reg CHECK ( (MedicamentRegulated = 1 AND DateValidity IS NOT NULL) OR MedicamentRegulated = 0 )Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Thursday, October 18, 2018 10:35 PM
But the column called DataValidity is also referenced in the constraint. SQL could just as easily use that column to determine which column the constraint applies to. So why would SQL use MedicamentRegulated instead of DataValidity to determine which column the constraint applies to?
-
Correct, the constraint is between the two columns, it applies to the table Medicaments
Let me repeat something I said earlier:
Other than default and not null, constraints are objects applied to the table. There aren't two types of check constraints. There are just check constraints. There's shorthand syntax where you can define the constraint with the column or at the end of the column list, that's just syntax.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Saturday, October 20, 2018 12:14 PM
Ok. I see what you mean now. But I have one more question. In the source code I tried, I have a table level constraint and it references two columns also. Why didn't that work? What is wrong with my syntax?
CREATE TABLE employeeMasterFile
(
Vendor varchar(30) NOT NULL,
Address varchar(30),
City varchar(20),
Email varchar(30),
State char(2),
Zip char(5)
CONSTRAINT CK_Zip CHECK(LEN(Zip)=LEN(State)+3)
)
-
You're missing a comma.
CREATE TABLE employeeMasterFile (
Vendor varchar(30) NOT NULL,
Address varchar(30),
City varchar(20),
Email varchar(30),
State char(2),
Zip char(5) ,
CONSTRAINT CK_Zip CHECK(LEN(Zip)=LEN(State)+3)
)For whatever syntatical reasons there are, if the constraint is part of the column definition, it seems to only be able to reference that column (although docs don't specify such a limitation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Saturday, October 20, 2018 1:43 PM
Ok. I added the comma and I am still getting an error:
Incorrect syntax near ')'.
I check the parentheses in the check constraint and I believe I closed off every opening parenthesis.
Here is my code with the comma:CREATE
TABLE employeeMasterFile
(
Vendor varchar(30) NOT NULL,
Address varchar(30),
City varchar(20),
Email varchar(30),
State char(2),
Zip char(5),
CONSTRAINT CK_Zip CHECK(LEN(Zip)=LEN(State)+3)
)
-
Nothing wrong with the code you posted. Error must be somewhere else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Sunday, October 21, 2018 5:06 AMNothing wrong with the code you posted. Error must be somewhere else.
I tried it again and it did work this time. Maybe I had some bits of previous code selected and didn't know it, when I ran the previous query.
Thank you for all of your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply