--- In firebird-support@yahoogroups.com, "Rick Roen" <rick@s...> wrote:

>
> 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

Rick,

In this case I would advise against using a check constraint, and
rather use a foreign key to a lookup table. You could still use the
domain if you wish to set the field type, size, and nullibility (word
for the day).

CREATE TABLE DEFAULTUNIT
(
VALUE VARCHAR(3) NOT NULL
PRIMARY KEY (VALUE)
);

COMMIT;
INSERT INTO DEFAULTUNIT(VALUE) VALUES ('LB');
INSERT INTO DEFAULTUNIT(VALUE) VALUES ('KG');
INSERT INTO DEFAULTUNIT(VALUE) VALUES ('OZ');
.... ETC

COMMIT;

Then something like

CREATE TABLE MYTABLE
(
ID BIGINT,
UNIT VARCHAR(3) NOT NULL DEFAULT 'LB'
PRIMARY KEY (ID)
FOREIGN KEY UNIT REFERENCES DEFAULTUNIT(VALUE) ON UPDATE CASCADE ON
DELETE CASCADE
)

(You will need to check my DDL for errors).

Then your lookup can be populated by

select value
from defaultunit

The foreign key constraint enforces the value to belong to your
required set, and you now have more flexibility in defining what
happens to values in MYTABLE when you change or remove a unit. Some
more advice, if you are going to default the LB, then create a before
delete trigger on defaultunit to raise an exception if this value is
deleted.


Adam