Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-02-24 : 05:40:52
|
| Hi, I need to put a constraint on a column, my problem is that the constraint I need is very complex and I'm not sure I wouldn't be better just comparing against a full list of all possible values (> 2 million). The constraint is for a UK postcode which is of the following format:Max 8 charactersThe 'outward code' can be either 2, 3 or 4 characters followed by a space and the 'inward code' which is three characters and is always NAA (N = numeric, A = Alphabetic) (this is the easy-ish bit) The outward code has seven valid formats:- AN, ANN, AAN, AANN, ANA, AANA and AAA3 (not sure what this one means or where it is used!!), the letters I and Z are not used in the second alphabetic position (except GIR 0AA)The second half of the code never uses the letters C, I, K, M, O, or V. There are also other restrictions that are currently in force but that may change in the future see http://www.govtalk.gov.uk/gdsc/html/frames/ and click on the post code linkAs suggested above I do have a list of all 2 million + post codes which I could alternatively use the check constraint to reference.Any suggestion appreciatedthankssteveSteve no function beer well without |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-02-24 : 05:57:20
|
| All the logic required is in the website link you provide so I would suggest that you implement your constraint no matter how complex it will be. Good luck.________________Make love not war! |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-24 : 06:19:22
|
| Going the check route....means keeping your code in sync with the govm't rules.Going the FK route....should be simpler...either the postcode exists in the file (and is valid) or it doesn't exist in the file (and is invalid)....Why should you care about the format? unless you want to format the displays properly....or control what the user is keying. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-02-24 : 06:59:17
|
| Simpler but perhaps less efficient?! a list of all 2 million records will most likely take more time to scan than a complex set of IF statements. So I would personally implement the check constraint.________________Make love not war! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-24 : 09:03:02
|
| Being continually accurate might be more important...than suffering the minor performance hit that is only invoked on insert....with the right index, it shouldn't be a show-stopper.However having the check-constraint out-of-sync with the 'official' rules might be. |
|
|
|
|
|
|
|
|
cheque please