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: Today @ 10:32 AM
Points: 393, Visits: 1,136
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
Posted Wednesday, August 13, 2014 4:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:34 AM
Points: 2, Visits: 11
[b]
2. In my opinion, Customer Numbers and Items Numbers don't belong to the numeric domain. I usually argue that these are string values which are restricted to digit characters. As just one argument, there are generally minimum and maximum length restrictions on such values which are hard to specify for integers, but are obvious for strings.


What about if you want to search for customer numbers 100-500

For example if a organisation wanted to know once they had their millionth customer or to check if targets were met?
Post #1602639
Posted Wednesday, August 13, 2014 8:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 1,945, Visits: 3,062
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 ..


Yes, I did and since you can name constraints, I use those names for violations. I have not seen de Haan & Koppelaar; I need to get a copy. Thanks!



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1602748
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse