help with coloumn values

[madhura09]

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

[Ramesh S]

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

Try this

CREATE TABLE YourTableName
( 
  column 1...,
  column2...,
  STATUS  numeric(1),
  CONSTRAINT check_status
  CHECK (STATUS IN (1,2,3)) 
);

[debasisdas]

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

try this

create TABLE table1 
(ID INT,
status varchar2(10) DEFAULT 'Available',
CONSTRAINT con_chk1 CHECK (status IN ('Available','Unavailable','CheckedOut')
))

[anubina]

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

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:

alter table itself add constraint ck_itself_status check ((status in ('Available','Unavailable','CheckedOut')));

[ferensick]

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.

[madhura09]

hey everyone
thanks a lot.
i think constraint is the way to go and its fitting perfectly with my database design.
thanks a lot