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?
GNOVAKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Rules of normalization dictate a lookup table with a foreign key to the main table.

I prefer to have separate lookup tables for my list of values even if there are only a couple of values.

It allows for maximum flexibility and reduced risk of data errors down the road.
GNOVAKAuthor Commented:
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?
slightwv (䄆 Netminder) Commented:
>>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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
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.
slightwv (䄆 Netminder) Commented:
>>use Excel or Access and then import it.

If Access, I would just use Linked Tables.  No import necessary.
sdstuberCommented:
if a simple warning is sufficient and you only have 3 values,  you could maybe try using a mnemonic check constraint


CREATE TABLE changes
(
    change_type VARCHAR2(20 BYTE),
    CONSTRAINT chk_plsql_vba_powerbasic CHECK
        (change_type IN ('PL/SQL', 'VBA', 'PowerBasic'))
        ENABLE VALIDATE
);

INSERT INTO changes(change_type) VALUES ('test');


ORA-02290: check constraint (YOURSCHEMA.CHK_PLSQL_VBA_POWERBASIC) violated


The constraint name tells me I should only use plsql, vba or powerbasic.

or, if you want a more explanatory (and case sensitive) error message you could implement a BEFORE trigger


CREATE OR REPLACE TRIGGER changes_briu
    BEFORE INSERT OR UPDATE
    ON changes
    FOR EACH ROW
BEGIN
    IF :new.change_type NOT IN ('PL/SQL', 'VBA', 'PowerBasic')
    THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Change_Type must be one of "PL/SQL", "VBA", or "PowerBasic"'
        );
    END IF;
END;

Now an invalid insert will produce this error message


ORA-20001: Change_Type must be one of "PL/SQL", "VBA", or "PowerBasic"
ORA-06512: at "YOURSCHEMA.CHANGES_BRIU", line 4
ORA-04088: error during execution of trigger 'YOURSCHEMA.CHANGES_BRIU'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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.
GNOVAKAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.