Link to home
Start Free TrialLog in
Avatar of GNOVAK
GNOVAKFlag for United States of America

asked on

Oracle 11g: Is a Drop Down literal Field possible?

I'm designing a table to track programitic changes (rudimentary version control). One of the fields will be one of three types of language: we currently deal with: PL/SQL, VBA, PowerBasic.
Typically I would have another table with a unique Type_key and a description. I would then use the Type_id in the main table.
Because the list is so limited, it would be more practicle to have a "drop down" field where the acceptable values would be "hard coded" to avoid mis-spellings.

Is something like this possible, or, is there anything I can do to accomplish this without creating yet another lookup table?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GNOVAK

ASKER

That's what I thought.
We typically have a lookup table but we do not place any hard coded relationships in oracle.
I'm going to be entering the information directly into the table (we utilize TOAD).

Is there a way that I can set up a "hard" relationship so that the Main table "lists" the acceptable values from the look up table?

For example, If the MAIN_TABLE has a field called PROGRAM_TYPE_ID and the LK_PROGRAM_TYPE table has two Fields: PROGRAM_TYPE_ID and PROGRAM_DESCRIPTION, is there a way that when I try to enter the information into the MAIN_TABLE, it would somehow indicate the possible PRGRAM_TYPE_IDs with the corresponding PROGRAM_DESCRIPTIONs?

or, what's the best solution for something like this other than having a better personal memory than I have?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>Is there a way that I can set up a "hard" relationship so that the Main table "lists" the acceptable values from the look up table?

That would be a feature of the tool you are using.  I'm not a Toad person so I don't know if you can create dropdown entries in something like a datasheet view with it.

I know this can be done in Access.
Toad does not have such a feature.

It's a rich tool for development and administration; but bulk, manual, data entry is not one of its strong suits.


If you have a lot of data entry you might want to use Excel or Access and then import it.
>>use Excel or Access and then import it.

If Access, I would just use Linked Tables.  No import necessary.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
But that requires the hard-coded list of values not a normalized lookup table.

If that suggestion was in answer to:
Is there a way that I can set up a "hard" relationship so that the Main table "lists" the acceptable values from the look up table?

Then I would go with a foreign key constraint to enforce that the value inserted was one of the acceptable ones.

Granted, you don't get the nice message of allowed values but you don't have hard-coded lists.
Avatar of GNOVAK

ASKER

Thanks !
I like using the constraint/trigger approach this time - wouldnt use it very often, but in this case it would work great.

Thanks again...
Great solution!