OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query [message #301405]

Wed, 20 February 2008 08:30

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

Report message to a moderator

Re: query [message #301407 is a reply to message #301405]

Wed, 20 February 2008 08:37

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ALTER TABLE
constraint

Regards
Michel

Report message to a moderator

Re: query [message #301468 is a reply to message #301407]

Wed, 20 February 2008 14:52

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: query [message #301492 is a reply to message #301468]

Wed, 20 February 2008 19:09

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

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

use CHECK constraint.

regards,

Report message to a moderator

Re: query [message #301534 is a reply to message #301520]

Thu, 21 February 2008 00:08

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.

Report message to a moderator

Re: query [message #301538 is a reply to message #301534]

Thu, 21 February 2008 00:25

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	i need one program urgently
Next Topic:	oracle apps

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 14:11:40 CST 2024