You talking to yourself Rick? ;)

I didn't see your suggestion when posting mine. Just one thing, the
existence check is not multitransaction safe. In other words, if
someone was to delete a VALID_UNIT in another transaction, then it
would still be visible to you, where using a foreign key you would
receive a foreign key violation. Depending on when these units are
set, this may not be an issue. MGA is terriffic sometimes, and other
times it can bite you when you don't consider how it affects isolation.

Adam

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@a...> wrote:
>
> CREATE TABLE VALID_UNITS
> (
> UNIT VARCHAR(3) NOT NULL,
> CONSTRAINT PK_VALID_UNITS PRIMARY KEY (UNIT)
> )
>
> CREATE DOMAIN DEFAULTUNIT AS
> VARCHAR(3)
> DEFAULT 'LB'
> NOT NULL
> CHECK (EXISTS (SELECT 1 FROM VALID_UNITS WHERE Upper(value) = UNIT))
>
> Query the VALID_UNITS table. Now DDL isn't required to add or remove
> units, and you don't have to query system tables. The only downside is
> that there are two places to update if the unit size changes from
> VARCHAR(3).
>
> Whichever way you do it, use a view to return the valid units, in order
> to hide the implementation.
>
> Rick DeBay
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Roen
> Sent: Thursday, December 29, 2005 8:47 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] extract constraint from Domain
>
> FB 1.5
>
> I have the following Domain:
>
> CREATE DOMAIN DEFAULTUNIT AS
> VARCHAR(3)
> DEFAULT 'LB'
> NOT NULL
> CHECK (Upper(value) in ('LB','KG','OZ','GM','EA','M'))
>
>
> From within my program I want to offer a combobox with these options in
> a fixed dropdown list for fields that use this domain.
>
> How can I extract the Check constraint from the System tables (or maybe
> just the entire DML) so I can parse out the valid values?
>
> Rick
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor --------------------~-->
> Fair play? Video games influencing politics. Click and talk back!
> http://us.click.yahoo.com/2jUsvC/tzNLAA/TtwFAA/67folB/TM
> --------------------------------------------------------------------~->
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item on
> the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>