Putting constraint in Oracle

Go down

Putting constraint in Oracle

Post by abhisheksingh on Thu Mar 18, 2010 12:44 pm

how to put following constraint in any column of the table.?

total number of char not to exceed 5,
first char must be 'D',
second char can be 'E','H' or 'T',
and 3rd, 4th, 5th char must be digits.[0-9].

for ex following are valid values : DE001, DH123, DT345 etc.
avatar
abhisheksingh

Posts : 10
Points : 117
Join date : 2010-03-09
Age : 31
Location : Haldia

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by abhisheksingh on Thu Mar 18, 2010 1:23 pm

I tried this..
create table disk_tab(disk_id varchar2(5) constraint disk_name1 check (disk_id like ('D[EHT][0-9][0-9][0-9]')), disk_name char(10));

table is created but the "disk_name1" constraint is violated whenever m trying to insert values such as DE123, DH001 etc..
avatar
abhisheksingh

Posts : 10
Points : 117
Join date : 2010-03-09
Age : 31
Location : Haldia

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by rohit_1000r on Thu Mar 18, 2010 4:36 pm

abhishek this kind of problem is caused due to incorrect expression we cannot use normal regular expressions with the CHECK like command hence try the following condition

CHECK( disk_id LIKE 'DE___' OR disk_id LIKE 'DH___' OR disk_id LIKE 'DE___')

rohit_1000r

Posts : 4
Points : 4
Join date : 2010-03-09

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by Christopher on Thu Mar 18, 2010 4:43 pm

'_' can take any character. he wants 3 last characters to be numbers.??
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: Putting constraint in Oracle

Post by urvershi on Thu Mar 18, 2010 5:10 pm

CREATE TABLE name_tab(NAME VARCHAR2(5),CONSTRAINT name_num check((name like 'DE___' or name like 'DH___' or name like 'DH___') and (substr(name,3,5) between 0 and 999)));

urvershi

Posts : 21
Points : 70
Join date : 2010-02-26

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by Christopher on Thu Mar 18, 2010 5:14 pm

ya... it works... Smile
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: Putting constraint in Oracle

Post by shivangi shah on Thu Mar 18, 2010 6:03 pm

if u apply a constraint like this "check (disk_id like 'DE[0-9][0-9][0-9]' or disk_id like 'DH[0-9][0-9][0-9]' or disk_id like 'DT[0-9][0-9][0-9]');" on a column called disk_id, the constraint is applied without showing any error... my question is what input will satisfy the above constraint? i have tried for input like "dh123" but this violates the constraint...
avatar
shivangi shah

Posts : 3
Points : 3
Join date : 2010-03-11
Age : 31
Location : Haldia

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by abhisheksingh on Thu Mar 18, 2010 6:04 pm

thnks frnds.. its working.. keep solving my probs. Smile
avatar
abhisheksingh

Posts : 10
Points : 117
Join date : 2010-03-09
Age : 31
Location : Haldia

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by Christopher on Thu Mar 18, 2010 6:23 pm

@ shivangi shah
it will accept "DE[0-9][0-9][0-9]","DH[0-9][0-9][0-9]" and "DT[0-9][0-9][0-9]". no significance of using square brackets and specifying range. it takes everything as characters.
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: Putting constraint in Oracle

Post by shivangi shah on Fri Mar 19, 2010 12:48 pm

yeah ok...thnks..Smile
avatar
shivangi shah

Posts : 3
Points : 3
Join date : 2010-03-11
Age : 31
Location : Haldia

View user profile

Back to top Go down

Re: Putting constraint in Oracle

Post by Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum