Re: Constraint problem

Svenn> Philip Lijnzaad wrote:
>> Anyway, in UML, this would look like:
>>
>> +-------------+ +---------------+
>> | Complex | | Reference |
>> |-------------|-----------|---------------|
>> | | + + | |
>> | | | |
>> +-------------+ +---------------+
>>
>> In this case, Complex <-> Reference is a many-to-many association for which
>> you need to have additional link table; see your text books.
>>
Svenn> So how would I make sure that for every complex has a reference and Svenn> every reference refers to a complex?

ah, now I see what you're after. In general, no this is not possible (although some vendors may allow you to set up fairliy flexible check constraints that do it. You'll have to suspend this constraint during INSERTing, though). First you INSERT stuff into Complexes, then into References, then the links table; when that's done, you explicitly check that all Complexes have at least one Complex_Reference, and likewise that all References have tat least one Complex_Reference. Then you commit.

You can do this in a trigger, but it's gonna be vendor-specific.

Cheers,

                                                                      Philip

-- 
/dev/brain:  character special (53/0)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-24
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53