Re: Prededined items in table

In article <wXIp6.4168$43.36542_at_zonnet-reader-1>, !!!!nijntje!!!! <No_Spamavanrossem_at_hotmail.com> writes
>Hello all,
>
>I have the following problem:
>
>I have a table in which users (through a user interface) can put data into.
>I want to establish that
>the user can select several items for a specific column. For example:
>
>I have a table called "user data" In this tables I have the following
>columns
>
>- Name
>- Adress
>- day of Birth
>
>The day of birth is monday, Tuesday, ..,.,.. ,Sunday.
>
>Now if the user has to fill in the day, He only can fill in the predefined
>values (monday...sunday)
>How can I do this IN ORACLE (so not in the user interface)

Use either foreign keys or check constraints.

For foreign keys you will need a table containing the allowable items with a primary key or unique index on the column and then define a foreign key in user_data.

e.g.

create table weekdays
( day_name varchar2(10) not null primary key);

insert into weekdays (day_name) values ('SUNDAY'); etc...
alter table user_data
add constraint xxx
foreign key (day_of_birth)
references weekdays;

This is the "right" way to do it but you could end up with lots of very small tables and lots of foreign keys which can hit efficiency and add a maintenance overhead.

Using check constraints do something like (I rarely use check constraints so the syntax may be off)

alter table user_data
add constraint xxxx
check ( day_of_birth in ('SUNDAY','MONDAY',...etc);

This comes with its own maintenance problems.
>
 

-- 
Jim Smith