| Author |
Topic |
|
sram3003
Starting Member
2 Posts |
 Posted - 2004-05-07 : 08:39:14
|
| You are my last chance...I have a problem creating a table in sql. I have to create an attribute called rating. The allowed data strings should be something like 'FSK0', 'FSK6' and so on. Only these strings should be allowed in the table. I think i could do this with a check constraint, but with which command i determine the data?Hopefully...sram |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-07 : 08:40:44
|
| In order to define your check constraint, you will need to be more precise than "...and so on".Jay White{0} |
 |
|
|
sram3003
Starting Member
2 Posts |
Posted - 2004-05-07 : 08:47:55
|
| OK, i will be more precisly. The allowed Data strings should be: 'FSK0', `FSK6', 'FSK12', 'FSK16', 'FSK18'; to write 'and so on' in the sql syntax was not my thought... |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-07 : 09:05:37
|
| [code]alter table <tablename>add constraint <constraint_name> check(rating in ('FSK0','FSK6','FSK12','FSK16','FSK18'))[/code] Jay White{0} |
|
|
|
Osangar
Starting Member
7 Posts |
Posted - 2005-03-16 : 09:41:27
|
| How do you create ranges for the check constraint ?Such as: S, followed by any letter, followed by any number between 1 and 99.So SB1 – SA99 SL34 …….ect ? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 09:44:36
|
this wil require SB01...S[a-zA-Z][0-9][0-9]Go with the flow & have fun! Else fight the flow  |
|
|
|
Osangar
Starting Member
7 Posts |
Posted - 2005-05-09 : 09:09:40
|
| I am getting errors indicating that there is a conflict with the constraint.I want to allow only data within the range of CB1 and CB999I have used the check syntax of constraint CK_BusinessOwnerNo_range check (BusinessOwnerNo like 'CB[1-9][0-9][0-9][0-9][0-9][0-9]'),I get the error when I try to insert the data: CB9Can anybody help ? |
|
|
|
mayur_patel
Starting Member
1 Post |
Posted - 2005-05-09 : 09:50:50
|
| Why you don't try this,constraint CK_BusinessOwnerNo_range check (BusinessOwnerNo like 'CB[1-999]') |
|
|
|
Osangar
Starting Member
7 Posts |
Posted - 2005-05-10 : 10:49:46
|
| Hi mayor patelThanks for the help, but I still get an error with that syntaxIn the enterprise manager the constraint looks like this:([BusinessOwnerNo] like 'CB[1-999]')I have tested it and I can only enter CB1, CB2, ….CB9 and that’s it, after that I get an error indicating I have violated the constraint. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-05-10 : 11:09:47
|
| for three digits you need to doCB[1-9][1-9][1-9][1-999] doesn't work.Go with the flow & have fun! Else fight the flow |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-05-10 : 16:57:02
|
quote:
Originally posted by spirit1
for three digits you need to doCB[1-9][1-9][1-9][1-999] doesn't work.Go with the flow & have fun! Else fight the flow
Mladen what about 10?? Try this...CHECK (BusinessOwnerNo LIKE 'CB[1-9]' OR BusinessOwnerNo LIKE 'CB[1-9][0-9]' OR BusinessOwnerNo LIKE 'CB[1-9][0-9][0-9]') The only way something like thisBusinessOwnerNo like 'CB[1-9][0-9][0-9]'would work is if the data entered was in the range CB001 to CB999 because it expects 3 numbers after the CB, so CB1 breaks the constraintAndyBeauty is in the eyes of the beerholder  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-10 : 17:04:46
|
quote:
Originally posted by Osangar
I am getting errors indicating that there is a conflict with the constraint.I want to allow only data within the range of CB1 and CB999I have used the check syntax of constraint CK_BusinessOwnerNo_range check (BusinessOwnerNo like 'CB[1-9][0-9][0-9][0-9][0-9][0-9]'),I get the error when I try to insert the data: CB9Can anybody help ?
if 'CB' is a constant, then it should not stored. Forget the varchar column, use an integer column and put a constraint on the range 1-999 and when you present your codes just append 'CB' and any leading zeroes if needed. Do not store redunant data in your database, and also don't make things more complicated then they need to be.- Jeff |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-05-10 : 17:10:46
|
quote:
....and also don't make things more complicated then they need to be.
Good point . Couldn't see the wood for the trees!Thats the missus rubbing off on me Beauty is in the eyes of the beerholder |
|
|
|
Osangar
Starting Member
7 Posts |
Posted - 2005-05-11 : 06:06:17
|
| Thanks for all the help guys, that has worked perfectly.Such a simple thing.Cheers, and have a nice day to all |
|
|
|
|
only certain data allowed in table?