At 10:11 PM 20-01-02 +0100, you wrote:

>table ART contains all the items sold by a warehouse.
>
>COD_ART is the record field that contains the item code.
>
>COD_BIS is the record field that contains the code of another item that can
>be sold in place of the current item if this is sold-out.
>
>I'd like to add a constraint that verifies that COD_BIS is NULL or is
>contained in the field COD_ART of a different record.
>
>How do I create this script ?

Constraints have to be valid at the time you create them, so you can't create a constraint that depends on the data content of a different record in the same table. You can create one that depends on the data content of the same record; and you can create one that depends on the data content of a column in a different table.

You can achieve what you want by triggers (Before Insert and Before Update). You can add exception handling, e.g. return an exception message or replace the submitted value with a default.

create trigger ta_validate_bi /* ta_validate_bu */
for ta
active before insert /* active before update */
position 0
as
begin
if (new.CODE_BIS is not null) then
if not (EXISTS (select COD_ART from ta where COD_ART = new.CODE_BIS)) then
new.CODE_BIS = null ;
end;

But I would not be comfortable with a validation that depends on user guesswork. The problem with using data in the same table for validation is that your transaction doesn't know what is happening in other transactions. Before your transaction commits, another transaction might post an insert or delete that invalidates your test and you could still get either a NULL where the supplied value would have been OK; or you could commit a non-null value that, in the interim, has been deleted.

I would prefer to normalise this relationship into another structure (a control table) containing each COD_ART and its corresponding valid CODE_BIS values (one unique row for each possible correspondence). Then you can use CHECK constraints; and multiple "hits" on a specific combination would be protected by concurrency control.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________