query [message #301405] |
Wed, 20 February 2008 08:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dsa09
Messages: 7 Registered: February 2008
|
Junior Member |
|
|
hi ppl, i have an existing table with a column sex whereby the data field is varchar(6)
i need to implement a script to change the length of the field to 1 and makes it a mandatory field that accepts only ‘M’ or ‘F’.
need advice as i'm doing sql the first time
|
|
|
|
|
Re: query [message #301492 is a reply to message #301468] |
Wed, 20 February 2008 19:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dsa09
Messages: 7 Registered: February 2008
|
Junior Member |
|
|
ok, i made the table to allow varchar2(1), but i still not sure how to add the Constraint to let sex only allows M or F inputs only.
i performed
alter table teacher add constraint sex_id unique (sex);
but the system gives me an error cannot validate (system.ID_SEX)
- duplicate keys found
[Updated on: Wed, 20 February 2008 19:15] Report message to a moderator
|
|
|
|
Re: query [message #301534 is a reply to message #301520] |
Thu, 21 February 2008 00:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dsa09
Messages: 7 Registered: February 2008
|
Junior Member |
|
|
ok i did the alter table teacher add constraint sex_chk check (sex in ('m','f'));
but it also returns error at line 1:
ora-02293: cannot validate(system.SEX_CHK) - check constraint violated.
|
|
|
Re: query [message #301538 is a reply to message #301534] |
Thu, 21 February 2008 00:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Obviously, not all sexes are 'm' or 'f'. How many sexes are there, anyway? I've heard of ~dozen or so, somewhere, someday.
See what's in there bySELECT sex, count(*)
FROM this_table
GROUP BY sex; and you'll know what you're dealing with. Then unify them to 'm' and 'f' and that should do it.
|
|
|