Re: Database wide constraint?

Thanks, both of you.

This is in re of my 'Table Driven Mail Merge' post.

So the answer looks like, theoretically possible, but not with current dbms systems.

Table 1 is the table of all possible letter sections (LetterTypeID, SectionTypeID, DefaultTextID) and tables 2...n are ones like CourseLetterSectionsText (CourseID, LetterTypeID, SectionTypeID, TextID) - that is to say the text blocks that vary according to Course.

At the moment the simplest thing looks like having an extra field in table 1, called something like VariesAccordingTo, with values like 'CourseType', 'JobType'.

Then in each of the others a constraint that checks the join. So CourseLetterSectionsText would have a constraint on the FK fields (LetterTypeID, SectionTypeID) that only allows a FK value if the row with the same PK value in table 1 contains 'CourseType' in the VariesAccordingTo field.

That feels a bit kludgy, but might work. At least the constraint will only need to be built once per table. I guess it feels kludgy cause there's a hard coded value in a constraint.

Also I'd rather have a constraint that a trigger, aren't constraints a bit more portable?

I may well be barking up the wrong tree with the whole structure, but I've looked at it from every angle.

Emily. Received on Wed Aug 09 2006 - 12:00:21 CEST