Oracle

help with coloumn values

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Nov 2007

Posts: 6

Reputation: madhura09 is an unknown quantity at this point

Solved Threads: 0

madhura09

Offline

Newbie Poster

help with coloumn values

Jul 31st, 2009

hi
I have a coulumn named Status which can have only three values:
1. Available (Default)
2. Unavailable
3. CheckedOut

How can I add this functionality while creating the table? i.e. while creating the table itself how can i specify that the column can hold only these 3 values and populate all rows with the default value?
Please help

God made the World
We program IT

•

Join Date: Jun 2009

Posts: 433

Reputation: Ramesh S will become famous soon enough

Solved Threads: 82

Ramesh S

Offline

Posting Pro in Training

Re: help with coloumn values

Aug 1st, 2009

Use check constraints. A check constraint allows you to specify a condition on each row in a table.

Try this

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
CREATE TABLE YourTableName
( 
  COLUMN 1...,
  column2...,
  STATUS  numeric(1),
  CONSTRAINT check_status
  CHECK (STATUS IN (1,2,3)) 
);
CREATE TABLE YourTableName
( 
  column 1...,
  column2...,
  STATUS  numeric(1),
  CONSTRAINT check_status
  CHECK (STATUS IN (1,2,3)) 
);

Last edited by Ramesh S; Aug 1st, 2009 at 4:00 am.

•

Join Date: Feb 2007

Posts: 2,093

Reputation: debasisdas will become famous soon enough

Solved Threads: 126

debasisdas debasisdas is offline

Offline

Postaholic

Re: help with coloumn values

Aug 1st, 2009

you nee to create a table with check constraint and default values.

try this

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE TABLE table1 
(ID INT,
status varchar2(10) DEFAULT 'Available',
CONSTRAINT con_chk1 CHECK (status IN ('Available','Unavailable','CheckedOut')
))
create TABLE table1 
(ID INT,
status varchar2(10) DEFAULT 'Available',
CONSTRAINT con_chk1 CHECK (status IN ('Available','Unavailable','CheckedOut')
))

Share your Knowledge.

•

Join Date: Jun 2009

Posts: 13

Reputation: anubina is an unknown quantity at this point

Solved Threads: 1

anubina

Offline

Newbie Poster

Re: help with coloumn values

Aug 1st, 2009

You can create the constraint at the moment of the creation of the table

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
DROP TABLE itself;
CREATE TABLE itself (
  status VARCHAR2(11) DEFAULT 'Available' CHECK (status IN ('Available','Unavailable','CheckedOut'))
);
drop table itself;
create table itself (
  status varchar2(11) default 'Available' check (status in ('Available','Unavailable','CheckedOut'))
);

Or you can create the constraint on the fly:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
ALTER TABLE itself ADD CONSTRAINT ck_itself_status CHECK ((status IN ('Available','Unavailable','CheckedOut')));
alter table itself add constraint ck_itself_status check ((status in ('Available','Unavailable','CheckedOut')));

•

Join Date: Feb 2008

Posts: 20

Reputation: ferensick is an unknown quantity at this point

Solved Threads: 4

ferensick

Offline

Newbie Poster

Re: help with coloumn values

Aug 7th, 2009

Pretty much as mentioned the constraints are the way to go. You can use a lookup table to easily do this as well. So you have your lkp_Status table, just with those 3 values. And you can create a primary foreign key constraint as well. This way in the future if a value is added and there needs to be 4 values you can just add the value in the lookup table and the primary foreign key relationship remains the same. It doesn't need to change.