FAQ
Is there any way you can specify that the only permissible values (is it
called a domain?) that can be entered in varchar2 field in an Oracle
table to, for example, A, B and C?

We can restrict what values users can enter at the application level,
but it would be nice to be able to also restrict what can be entered at
the database level, in case other means of entering data are ever used
or if the application layer fails, for whatever reason, to trap an
unwanted value.

Thanks

--
Aidan Whitehall
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775

This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Aidan Whitehall
INET: AidanWhitehall_at_Fairbanks.co.uk

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Jamadagni, Rajendra at Jan 30, 2003 at 4:34 pm
    create or replace trigger my_restrictions on
    my_table after insert or update as
    declare

    unacceptable_values exception;
    begin
    begin
    if :new.my_restricted_column not in ('Blah1','blah2','Blah3') then

    Grrr .. user errored
    spank_user;
    raise unacceptable_values;
    end if;
    end;
    exception
    when unacceptable_values then
    raise_application_error(20001,'You entered incorrect values, go spank
    yourself.');
    when others then
    raise;
    end;
    /

    this should pretty much handle it for you ... don't take this code at its
    face value, there could be syntactic errors .. I just wrote on the fly.

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    -----Original Message-----
    Sent: Thursday, January 30, 2003 11:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: ESPN_Disclaimer.txt
  • Igor Neyman at Jan 30, 2003 at 5:31 pm
    Read docs about CHECK constraint.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com


    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, January 30, 2003 11:00 AM
    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.


    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    ________________________________________________________________________
    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk
    ________________________________________________________________________
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Igor Neyman
    INET: ineyman_at_perceptron.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Pat Hildebrand at Jan 30, 2003 at 5:31 pm
    Sounds made to order for a check constraint on the column of interest.

    Pat
    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.


    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Pat Hildebrand
    INET: pat_at_ssc.upenn.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Pardee, Roy E at Jan 30, 2003 at 5:41 pm
    How about:

    alter table my_table add (constraint domain_ck check (my_column in ('A',
    'B', 'C') ) );

    Cheers,

    -Roy

    Roy Pardee
    Programmer/Analyst
    SWFPAC Lockheed Martin IT
    Extension 8487
    -----Original Message-----
    Sent: Thursday, January 30, 2003 8:34 AM
    To: Multiple recipients of list ORACLE-L

    create or replace trigger my_restrictions on
    my_table after insert or update as
    declare

    unacceptable_values exception;
    begin
    begin
    if :new.my_restricted_column not in ('Blah1','blah2','Blah3') then

    Grrr .. user errored
    spank_user;
    raise unacceptable_values;
    end if;
    end;
    exception
    when unacceptable_values then
    raise_application_error(20001,'You entered incorrect values, go spank
    yourself.');
    when others then
    raise;
    end;
    /

    this should pretty much handle it for you ... don't take this code at its
    face value, there could be syntactic errors .. I just wrote on the fly.
    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    -----Original Message-----
    Sent: Thursday, January 30, 2003 11:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?
    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks
    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Pardee, Roy E
    INET: roy.e.pardee_at_lmco.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Fink, Dan at Jan 30, 2003 at 6:00 pm
    Check Constraint!

    -----Original Message-----
    Sent: Thursday, January 30, 2003 9:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Koivu, Lisa at Jan 30, 2003 at 6:35 pm
    Yes. Read up on check constraints.

    Lisa Koivu
    Oracle Diaper Administrator
    Fairfield Resorts, Inc.
    5259 Coconut Creek Parkway
    Ft. Lauderdale, FL, USA 33063

    -----Original Message-----
    Sent: Thursday, January 30, 2003 11:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Koivu, Lisa
    INET: Lisa.Koivu_at_efairfield.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mercadante, Thomas F at Jan 30, 2003 at 6:45 pm
    look at foreign key constraints - data in columns allowed only if the value
    exists in another table. best resolution because you can add values without
    recompiling any code.

    -----Original Message-----
    Sent: Thursday, January 30, 2003 11:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jared.Still_at_radisys.com at Jan 30, 2003 at 8:55 pm
    I concur.

    Then the duhvelopers don't have to bug you when they
    determine that their 'never changing' list of values
    was incomplete.

    Jared

    "Mercadante, Thomas F"
    Sent by: root_at_fatcity.com
    01/30/2003 10:45 AM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Restricting the range of values in a field

    look at foreign key constraints - data in columns allowed only if the
    value
    exists in another table. best resolution because you can add values
    without
    recompiling any code.

    -----Original Message-----
    Sent: Thursday, January 30, 2003 11:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Fink, Dan at Jan 30, 2003 at 10:35 pm
    Of course here is my favorite constraint story...

    column has NOT NULL constraint and CHECK constraint with list of values
    including ('NULL')

    -----Original Message-----
    Sent: Thursday, January 30, 2003 1:56 PM
    To: Multiple recipients of list ORACLE-L

    I concur.

    Then the duhvelopers don't have to bug you when they
    determine that their 'never changing' list of values
    was incomplete.

    Jared

    "Mercadante, Thomas F"
    Sent by: root_at_fatcity.com
    01/30/2003 10:45 AM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: RE: Restricting the range of values in a field

    look at foreign key constraints - data in columns allowed only if the
    value
    exists in another table. best resolution because you can add values
    without
    recompiling any code.

    -----Original Message-----
    Sent: Thursday, January 30, 2003 11:01 AM
    To: Multiple recipients of list ORACLE-L

    Is there any way you can specify that the only permissible values (is it
    called a domain?) that can be entered in varchar2 field in an Oracle
    table to, for example, A, B and C?

    We can restrict what values users can enter at the application level,
    but it would be nice to be able to also restrict what can be entered at
    the database level, in case other means of entering data are ever used
    or if the application layer fails, for whatever reason, to trap an
    unwanted value.

    Thanks

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    INET: Jared.Still_at_radisys.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Fink, Dan
    INET: Dan.Fink_at_mdx.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Aidan Whitehall at Jan 31, 2003 at 9:26 am
    Yes. Read up on check constraints.
    Thanks for everyone's suggestions and code. I'll go with the check
    constraint in this instance because 10-or-so lookup tables with 2-to-5
    values in each that will never change seems like overkill. Plus, the
    other developer here isn't a fan of lookup tables where the range of
    values are known to be static and he'd shout at me;-)

    Thanks again!

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    ________________________________________________________________________
    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk
    ________________________________________________________________________
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Alex Andriyashchenko at Jan 31, 2003 at 11:33 am
    Hello Aidan,

    Thursday, January 30, 2003, 4:00:40 PM, you wrote:

    AW> Is there any way you can specify that the only permissible values (is it
    AW> called a domain?) that can be entered in varchar2 field in an Oracle
    AW> table to, for example, A, B and C?

    AW> We can restrict what values users can enter at the application level,
    AW> but it would be nice to be able to also restrict what can be entered at
    AW> the database level, in case other means of entering data are ever used

    AW> or if the application layer fails, for whatever reason, to trap an
    AW> unwanted value.

    AW> Thanks

    Use CHECK constraint.

    Like
    ALTER TABLE a1
    ADD CONSTRAINT CHECK column1 IN ('A','B','C')

    --
    Best regards,

    Alex mailto:Oleksandr_A_at_yahoo.co.uk

    __________________________________________________

    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Alex Andriyashchenko
    INET: Oleksandr_A_at_yahoo.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mercadante, Thomas F at Jan 31, 2003 at 12:38 pm
    Aidan,

    please remember us when the list of "2-to-5 values that never change"
    change. and we tell you "we told you so".

    and why do you care if the developer will get mad? he's wrong. you know, I
    know it and everyone on this list knows it.

    foreign keys are the final answer.

    that is all.

    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Friday, January 31, 2003 4:27 AM
    To: Multiple recipients of list ORACLE-L
    Yes. Read up on check constraints.
    Thanks for everyone's suggestions and code. I'll go with the check
    constraint in this instance because 10-or-so lookup tables with 2-to-5
    values in each that will never change seems like overkill. Plus, the
    other developer here isn't a fan of lookup tables where the range of
    values are known to be static and he'd shout at me;-)

    Thanks again!

    --
    Aidan Whitehall
    Macromedia ColdFusion Developer
    Fairbanks Environmental Ltd +44 (0)1695 51775

    ________________________________________________________________________
    This e-mail has been scanned for all viruses by Star Internet. The
    service is powered by MessageLabs. For more information on a proactive
    anti-virus service working around the clock, around the globe, visit:
    http://www.star.net.uk
    ________________________________________________________________________
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Aidan Whitehall
    INET: AidanWhitehall_at_Fairbanks.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 30, '03 at 4:00p
activeJan 31, '03 at 12:38p
posts13
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase