Unix C++
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Putting constraint in Oracle

5 posters

Go down

Putting constraint in Oracle Empty Putting constraint in Oracle

Post by abhisheksingh 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.
abhisheksingh
abhisheksingh

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

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

Post by abhisheksingh 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..
abhisheksingh
abhisheksingh

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

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

Post by rohit_1000r 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

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

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

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

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

https://unixcpp.forumotion.com

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

Post by urvershi 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

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

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

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

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

https://unixcpp.forumotion.com

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

Post by shivangi shah 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...
shivangi shah
shivangi shah

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

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

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

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

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

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

Post by Christopher 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.
Christopher
Christopher
Admin

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

https://unixcpp.forumotion.com

Back to top Go down

Putting constraint in Oracle Empty Re: Putting constraint in Oracle

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

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

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

Back to top Go down

Putting constraint in Oracle Empty 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