Personally I'd prefer the foreign key, but my doppelganger may be having
issues with them due to FB 1.5 issues.
And IMNSHO the check constraint should be TX safe the same as a FK,
having the same violations.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Thursday, December 29, 2005 6:24 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: extract constraint from Domain

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
>

------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet
Life.
http://us.click.yahoo.com/KIlPFB/vlQLAA/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