Ad:

Oracle Discussion Thread
Marked Solved
Views: 877

Jul 31st, 2009

help with coloumn values

Expand Post »

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

Similar Threads

passing values of Select box from JSP to Action class thru form Bean in JSP
Gridview coloumn data to come in Bulleted list in ASP.NET
displaying the values in the combobox in VB.NET
ASCII values and characters in C
Add values of cl in Visual Basic 4 / 5 / 6

madhura09

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2007

Permalink

Aug 1st, 2009

Re: help with coloumn values

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

Try this

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.

Ramesh S

Reputation Points: 165

Solved Threads: 113

Posting Pro

Offline

580 posts
since Jun 2009

Permalink

Aug 1st, 2009

Re: help with coloumn values

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

try this

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')
))

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Aug 1st, 2009

Re: help with coloumn values

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

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:

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')));

anubina

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

17 posts
since Jun 2009

Permalink

Aug 7th, 2009

Re: help with coloumn values

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.

ferensick

Reputation Points: 33

Solved Threads: 4

Newbie Poster

Offline

20 posts
since Feb 2008

Permalink

Aug 7th, 2009

Re: help with coloumn values

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

madhura09

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: vista DSN name oracle odbc driver missing

Next Thread in Oracle Forum Timeline: Internal Server Error

DaniWeb Message

Cancel Changes

User Name
Password