# SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 22 of 35

Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

## Check Constraints

My old track coach would tell us to give 110% effort. However, had my math teacher heard this, he would have explained that a percentage value exceeding 100% in this context is not possible. For the coach it was a fun way that implies that you will give all you have, but then somehow you will give 10% more than “all.” Does that mean 110% is always an incorrect value? No, it depends on the situation. For example, this year’s sales could be 110% of last year’s sales. The range of acceptable values defined for a field depends upon the context.

Let’s consider negative numbers for a moment. If a medical form asked for my height in inches, and I wrote negative -67.5, you would think this was either a typo or a joke.  -67.5 would be easily accepted by a decimal or float data type, but in the context of height, a negative number simply isn’t acceptable. A person’s height will always be a positive number, never a negative number. For that reason, a “Height” field in a database should be restricted to only positive values. If you want to add your own logic on top of a data type, you can use what is known as a check constraint.

In the StateList the “District of Columbia” record should consist of 68 square miles of land. However, we see a negative LandMass value for that record and a few other records.

Similar to the concept of height, there really is no such thing as a negative land mass (coastal erosion anomalies aside).  We know that the absolute value for each LandMass number in our table is correct, so we essentially just need to clean up any negative figures to positive numbers (e.g., -68 should be 68).  The UPDATE statement below multiplies the LandMass value by -1 where the existing LandMass value is < 0 (less than zero). The SELECT query in the below Figure shows that no negative LandMass values remain following the update.

### Creating Check Constraints

Our LandMass data has been scrubbed and now we want to create the check constraint to maintain this field’s data integrity during future inserts or updates. We want SQL Server to say “Yes” to legitimate LandMass values (even if 2 states are the same size) but “No” to any update or insert resulting in a negative LandMass values. The code below will check to see if the value is positive before accepting the transaction. The preferred naming convention for the check constraint is CK for check (constraint) plus the table name and the fieldname, with each part separated by an underscore: CK_StateList_LandMass.

`ALTER TABLE StateList ADD CONSTRAINT CK_StateList_LandMass CHECK(LandMass >=0)`

To prove that our check constraint will prevent DML statements which try to introduce negative LandMass numbers, we will run a quick test. Below you see an UPDATE statement which attempts to change some of the LandMass values into negative numbers. The CK_StateList_LandMass check constraint guards against updates which would result in invalid LandMass values.

In the figure below we see the CK_StateList_LandMass check constraint that we just added. In Object Explorer, expand the “Tables” folder of the JProCo database and expand Constraints.  Note: You may need to right-click to refresh the Constraints folder and see newly created items.

Let’s consider other errors which could occur in our LandMass data. Our largest current LandMass is Alaska at just over 650,000 square miles. A typo adding one or two extra zeros would really be a problem (i.e., distorting Alaska’s LandMass to either 6 million or 65 million miles).  Let’s attempt a bad UPDATE to help us recognize that our check constraint isn’t currently protecting our table from these kinds of mistakes.

`UPDATE StateList SET LandMass = 65642500 WHERE StateID = 'AK' `

Since the UPDATE succeeded, our StateList table now incorrectly indicates that Alaska’s LandMass exceeds 65 million square miles. While constraints can’t guard against all potential data integrity problems, we should constrain the upper limit of the LandMass field in this example to disallow such glaringly large, erroneous values. Before proceeding, let’s reset Alaska’s LandMass back to its proper value:

`UPDATE StateList SET LandMass = 656425 WHERE StateID = 'AK' `

### Changing Existing Check Constraints

We want to add another condition to CK_StateList_LandMass, our existing check constraint for the LandMass field. There isn’t a T-SQL statement which allows us to directly modify our existing check constraint.  But we can easily accomplish our purpose by dropping the current constraint object and then rebuilding it with both conditions (values for LandMass must be non-negative and less than 2 million square miles).  Re-create CK_StateList_LandMass with expressions covering both of the conditions which we want the constraint to prevent (LandMass >= 0 and LandMass < 2000000) with the following formula.

`ALTER TABLE StateList DROP CONSTRAINT CK_StateList_LandMass GO ALTER TABLE StateList ADD CONSTRAINT CK_StateList_LandMass CHECK(LandMass >=0 AND LandMass < 2000000) GO`

To test whether our revised constraint works, let’s reattempt our earlier UPDATE statement. Our second requirement (LandMass < 2000000) is similarly enforced by the revised check constraint. Values in excess of 2 million (square miles) are disallowed for the LandMass field. You may also wish to perform additional testing to observe the check constraint allows valid transactions into the StateList table.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter1.0Setup.sql script from Volume 4.

## Question 22

You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

1. CHECK (Rating BETWEEN 1 and 100)
2. CHECK (Rating <=90 AND Complaint = 1)
3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
4. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

### Rules:

Every day one winner will be announced from United States.
Every day one winner will be announced from India.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

## SQL SERVER – Fix Error – 948 A downgrade path is not supported. The database cannot be opened because it is version.

• I believe you intended #3 to be the correct answer

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

However I believe there is a typo and it should read:

CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Reasoning: The first two options only enforce one of the two conditions. The fourth will not work because of the AND between the two conditions. Nothing will insert because the Complaint column is either a 0 or 1 and the AND between the two conditons pretty much makes it so it needs to be both. That leaves the third option. It uses OR and has information enforcing both constraints, however you said the rating could be from 0 to 100 and the third option eliminates the possiblity of entering 0. However I would assume if someone is entering 0 then they must have a value where complaint = 1. #3 would be correct if the first 1 was a 0.

Deb – USA

• Partha Dutta Gupta
August 23, 2011 12:06 am

None of the options are correct.

The first option check for rating and not complaint. Thus a value of 1 in complaint and more than 90 would be considered valid and it dos not serves our purpose.

The second option restricts complaint to 1 and rating less than =90. Thus a 0 value in complaint and value of between 91 and 100 in rating would be invalid and thus it does not server our purpose.

The third option is the most closest. but as our purpose is–> If customer complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Thus the third constraint would restrict us to put a value of Zero(0) in rating with complaint =1 . Thus this option will not work.

The fourth constraint is just the worst as the Complaint column would have to be both Zero(0) and 1 in same record which is impossible.

Country –> INDIA

• Sudeepta Ganguly
August 23, 2011 12:12 am

None of the above answers are correct. I think the correct option will be
CHECK ( (Rating BETWEEN 0 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Sudeepta,
India.

• I believe 3 is the correct answer

CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0)

Eric
USA

• You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

CHECK (Rating BETWEEN 1 and 100)
CHECK (Rating <=90 AND Complaint = 1)
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Sol :

The correct answer is Option 3.

Option 1 : This wouldn't work since we are not checking for Complaint column
Option 2 : This wouldn't work since we are not checking for Complaint =0 and rating between 1 and 100
Option 3 : This would work since we are checking for Complaint =1 and rating between 0 and 90 or complaint = 0 and rating between 1 and 100.
Option 4 : This wouldn't work since we are checking for Complaint =1 and rating between 0 and 90 and complaint = 0 and rating between 1 and 100.
thereby checking for both the conditions which is not right.

Raj Ejanthkar
USA

• Srikanth Nallamothu
August 23, 2011 10:05 am

• Correct Answer is Option 3.

3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Pratik Raval
India

• Q 22) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 22 of 35

A.) 3

Winner from USA: Prerana Sharma

Winner from India: santosh.s

I thank you all for participating here. The permanent record of this update is posted on facebook page.

• 3.CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Gordon
USA

• Correct answer is : C

Shiv Rampal
USA

• Option 3 is True.
AzHAR Iqbal
Pakistan

• None of the options are “exactly” true. Based on the wording of the problem,
If the customer has no complaint (Complaint = 0) then their rating can be between 1 and 100 (Rating BETWEEN 1 AND 100). If the customer does have a complaint (Complaint = 1) then they can only answer a score between 0 and 90 (Rating BETWEEN 0 AND 90)

Option 1 disregards whether the customer has a complaint or not
Option 2 doesnt consider if the customer does not have a complaint
Option 3 is the “most” correct but doesn’t include the correct range for ratings as stated in the question
Option 4 is impossible because the customer can’t have a complant AND not have a complaint.

The correct constraint would be

CHECK ((Complaint = 0 AND Rating BETWEEN 1 and 100) OR (Complaint = 1 AND Rating BETWEEN 0 and 90))

Country of Residence: USA