Home » Other » Training & Certification » query
query [message #301405] Wed, 20 February 2008 08:30 Go to next message
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 #301407 is a reply to message #301405] Wed, 20 February 2008 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALTER TABLE
constraint

Regards
Michel

Re: query [message #301468 is a reply to message #301407] Wed, 20 February 2008 14:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just note that you can not shrink the column if there are values which are larger than the desired (new) column length.

In other words: if there's 'Male' entered into the 'sex' column and you'll try to make it a VARCHAR2(1), it won't work. First you'll have to shorten column values ('Male' -> 'M', for example) and then alter the table.

Also, don't try to apply the NOT NULL constraint if there already are some records which don't have a value in the 'sex' column. Fill it first, then make it a NOT NULL.
Re: query [message #301492 is a reply to message #301468] Wed, 20 February 2008 19:09 Go to previous messageGo to next message
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 #301520 is a reply to message #301492] Wed, 20 February 2008 23:24 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
use CHECK constraint.

regards,
Re: query [message #301534 is a reply to message #301520] Thu, 21 February 2008 00:08 Go to previous messageGo to next message
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
Littlefoot
Messages: 20901
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 by
SELECT 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.
Previous Topic: i need one program urgently
Next Topic: oracle apps
Goto Forum:
  


Current Time: Fri Dec 09 17:20:35 CST 2016

Total time taken to generate the page: 0.46644 seconds