Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Stairway to Database Design STEP 2: Domains, Constraints and Defaults Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 5:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:21 PM
Points: 305, Visits: 1,017
Tom Bakerman (5/5/2010)
Thanks. It was that extra CHAR(5) that was throwing me off.

And for readability, would it help to put some extra () like this:

CHECK ((CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
WHEN floob_score = 42
AND fuzz_nbr = 17
THEN 'T' ELSE 'F' END) = 'T')



Thanks. That helped me understand what it was meant to do.
Post #1488071
Posted Saturday, August 24, 2013 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 24, 2013 9:17 AM
Points: 1, Visits: 0
Wonderful.

Share a website with you ,

( http://www.shoes2.us// )

Believe you will love it.

We accept any form of payment.

http://www.shoes2.us/Michael-Kors-Handbags-n2447/
Post #1488129
Posted Wednesday, November 6, 2013 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 7:46 AM
Points: 2, Visits: 30
There is only one case where that CHECK constraint will allow a row to be inserted: when floob_score = 42 AND fuzz_nbr = 17. So the constraint can be simplified as two column-level CHECK constraints... and considering the values are fixed, we can also add DEFAULTs:

...
floob_score INTEGER DEFAULT 42 NOT NULL
CHECK ( floob_score = 42 ),
fuzz_nbr INTEGER DEFAULT 17 NOT NULL
CHECK ( fuzz_nbr = 17 )
...

Presumably there would additionally be a FOREIGN KEY constraint(s), otherwise what would be the point of two columns with immutable values?

As a rule of thumb, a single complex CHECK constraint that tests many rules is to be avoided, something I thought Celko himself recommended in his "SQL Programming Style" book. It is certainly recommended by de Haan, and Koppelaar in "Applied Mathematics for Database Professionals", a book I know Celko is familiar with:

"It is good practice to write all tuple constraints in conjunctive normal form.... you’ll end up with as many conjuncts as possible... you would create one declarative check constraint per conjunct. This in turn has the advantage that the DBMS reports violations of tuple constraints in as detailed a way as possible."

As for the pattern

CHECK ( 'T' = CASE..'T'..ELSE..'F' END )

it is unnecessarily clunky. Presumably is an attempt to bring two-valued logic to SQL. There is a better way: avoid nulls entirely!
Post #1511873
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse