December 26, 2007 at 10:03 am
I am working with Check Constraints and was wondering if anyone had any recommendations for some articles that specifically talked about Check Constraint expressions dealing with multiple columns.
I have a several tables that I need to ensure when data is inserted into one column that it is also inserted into several other columns. I have been working with the NOT expression (which I don't 100% understand) and I wanted to get a clear picture of what I can and can't do.
TIA.
December 27, 2007 at 5:16 pm
I'm not really sure what you're asking for. If you have a table where several columns must contain the same value, that's easy. Say you have a table where columns A, B and C must always be the same.
alter table dbo.SomeTable add constraint
CK_ConstraintName check (a = b and b = c)
This is not (I hope) a real world constraint: why have different columns if they must be the same? How about something that would be actually useful? Let's say the columns specify the sides of a triangle and the check is for a valid triangle.
alter table dbo.Triangles add constraint
CK_ValidSides check (a + b > c and a + c > b and b + c > a)
Just write a Boolean expression inside the "()" that evaluates to True when the data is the way you want.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
December 28, 2007 at 7:21 am
Thanks for the reply. I am not making several columns the same. Actually what I am doing is when one column has a value the other columns must have a value also.
This is very similiar to the BOL example:
"This sample constraint guarantees that if the order is placed on a credit card, then only Visa, MasterCard, or American Express is accepted:
NOT (payment_method = 'credit card') OR
(card_type IN ('VISA', 'MASTERCARD', 'AMERICAN EXPRESS')) "
While I've gotten the same logic to work on another table I am having trouble reproducing a successful constraint with the same logic. The NOT statement is confusing to me anyways that is why I was looking for some good articles.
Here is an example of one that I wrote that does work:
(((not([Package_Type] = 'P' and (isnull([Length],0) < 1 or isnull([Width],0) < 1 or isnull([Height],0) < 1)))))
I was hoping for some good reading that could explain more about constraint expressions. I have several books from Ken Henderson but they don't go into it very much.
December 28, 2007 at 8:04 am
I'm not sure of a good beginning article on the subject. Basically, it's just a way to express boolean checks on data. Articles on that subject tend to get very thick, very fast. (Look up boolean logic in Wikipedia and you'll see what I mean.)
Basically, it breaks down to AND, OR, and NOT.
NOT means that the following test comes up false. Otherwise, it checks to make sure the test comes up true.
For example, if you want to make sure a column never has a 0 in it, you could check:
col1 <> 0
Which would return "true" if the column is greater than or less than 0. You could also check:
Not (col1 = 0)
Which returns true if the column is greater than or less than 0, or is null (since null is not equal to 0).
The first test assumes that the column will always have some value in it. The second allows for unknown values, just so long as the value isn't exactly 0.
Another example is checking the time on a datetime column. Let's assume you want to prevent times between noon and 4 PM, for whatever reason.
A "positive" test could be:
datepart(hour, col1) < 12 or datepart(hour, col1) > 16
which will return true if the time is less than 12 or more than 16 (4 PM). You could modify it to include the possibility of null values:
datepart(hour, col1) < 12 or datepart(hour, col1) > 16 or col1 is null
You could also use:
not(datepart(hour, col1) between 12 and 16)
which will return true so long as the hour is not between 12 and 16 (since between includes the values listed in T-SQL), which again would allow for null (unknown).
Basically, you want the check constraint to return true for okay values and false for forbidden values. Not simply inverts the true/false for the check it is applied to.
You can apply "not" to multiple checks by enclosing them in parentheses after the word "not". For example:
not(datepart(hour, col1) between 12 and 16 and col2 = "whosis")
In that case, if col2 was any value other than "whosis", it would allow for times between 12 and 16, but if col2 = "whosis", it disallows times betwen 12 and 16. The "and" means that both parts have to be true for the test to come out true. If both are true, the test is true, but the "not" in front of it inverts that and makes it "false".
If it's changed to:
not(datepart(hour, col1) between 12 and 16 OR col2 = "whosis")
Changing the "and" to "or", the test inside the parentheses comes up true if either the time is between 12 and 16, or col2 = "whosis", and the "not" inverts that. This one doesn't really make sense as a single constraint in any circumstance I can think of, but I'm using it to show the difference made by changing "and" to "or".
I hope that helps. Maybe someone else knows a good, simple article on the subject. I've had to figure it out myself based on a lot of articles that seem to be written by people who assume you already understand the subject you're reading about.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2007 at 9:47 am
Thanks for the response. It was definitely helpful.
December 28, 2007 at 11:34 am
You steadfastly refuse to go into enough detail for us to determine if you can do what you want in a constraint or not. The one example you gave, "(((not([Package_Type] = 'P' and (isnull([Length],0) < 1 or isnull([Width],0) < 1 or isnull([Height],0) < 1)))))" is fairly straightforward. You can get rid of the first three and last three parens, but it says "if Package is 'P' then Length, Width and Height must be greater than zero. If the NOT is confusing, a little Boolean algebra simplifies it to
"Package <> 'P' or (IsNull(Length,0) > 0 and IsNull(Width,0) > 0 and IsNull(Height,0) > 0)"
Whether or not you can perform the check in a constraint or trigger depends mostly on whether or not you can write the check as a boolean equation. In constraints, you can't have executable code or compare the values to values in other rows in the table or other tables. Actually, there is a way around that using UDFs but so far, I can't determine if you need to do that.
Or look at it like this. You only need triggers if you want to alter or add data, such as in a DateCreated or DateUpdated column or perform an insert or update to another table such as a history table. I am ready to be corrected but I don't think a trigger is necessary if all you're doing is performing a validity check on the data.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
December 28, 2007 at 12:38 pm
A trigger can be necessary for checking data validity if the validity is dependent on data in other tables or other rows of the same table, and can't be satisfied with a simple foreign key.
For example, if table1.col1 = 1 then table1.col2 has to be a value from table2 where table2.colA is between Jan and Mar, but if table1.col1 = 2 then table1.col2 has to be a value from table3 where table3.colX < 30, and so on. Such rules in order processing databases, based on complex business requirements, can't be enforced with a simple constraint, and pretty much require a trigger to enforce at the database level.
But what the original post was asking about was simple articles on the subject of constraints and specifically on what "not" does. He may or may not need help on a specific constraint, but he definitely was looking to expand his knowlege of the subject. If he wasn't looking for specific help on a specific constraint, then not including the constraint doesn't hinder supplying the requested information (data about how constraints work and how "not" works in them).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2007 at 12:53 pm
Thanks for the reply and the extra info!
You are right I don't need a trigger.
I was getting inconsistant results with various tests I was doing so I didn't think it could be done. I pressed on with my testing and I was able to write a constraint that doesn't allow only one out of the three required columns to be updated.
As far as the Parenthesis that's just how SQL automatically formats it after I save the constraint... weird.
December 28, 2007 at 1:32 pm
I just thought of another reason for using triggers in validation. We use it here in some of our tables. We have a workflow value and it can be changed only according to a certain sequence. So we have to know what it is being changed from to check what it is being changed to. Which means we have to access the inserted and deleted pseudo tables. Which means triggers -- and there is no UDF way around it.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply