Re: constraining one correct answer but multiple invalid answers

jcharpak_at_att.net wrote:
> Pretend there's a table called questions. It has two columns,
> question_number, and question_text. Question_number is the primary key
> and is artificial. Next, pretend there's a table called answers. It
> contains four columns: question_number (a foreign key to questions),
> answer_number, answer_text, and correct_yn which will contain either a
> 'Y' or 'N'. Answer_number will not be unique across all questions
> although it will be unique within a question. The primary key of
> answers is a composite key of question_number and answer_number. You
> can pretend there's a check constraint on correct_yn if you'd like.
>
> Each question can have any number of incorrect answers, but only one
> correct answer.
>
> I'm trying to come up with some kind of constraint or trigger that will
> enforce this restriction. One solution is to add a
> correct_answer_number column to questions and create a composite
> foreign key back to answers. For various reasons this isn't as easy as
> I've made it sound.
>
> Another possibility is to create a pre-insert/update/delete row level
> trigger to store which questions had their answers
> inserted/updated/deleted in a pl/sql table and then enforce the
> "constraint" in a statement level trigger. The problem here is that if
> the correct_yn column is updated one row at a time, statement level
> triggers become, in essence, row level triggers. This makes the order
> of update important. If I set a preexisting "y" to an "n" and then
> subsequently change a different "n" to a "y" there is no problem.
> However if a "y" already exists but I first change an "n" to a "y" and
> then change the first "y" to an "n", even though at the end of the
> transaction the data is valid, it isn't valid at the statement level.
> Considering the data is being entered in an Oracle Forms master-detail
> layout, the chances of users checking and unchecking the correct_yn
> fields of multiple answers in one transaction are quite high.
>
> I suppose I could loop though the records in a pre-commit trigger in
> the form and enforce the validation that way. However, that means the
> validation is at the application level and not at the database level.
>
> Does anyone have opinions on what would the best route to take? Are
> there alternatives I haven't considered?

Sorry I should have added don't even consider hacking up some kind of trigger based validation. That type of approach produces applications that don't scale, don't maintain well, and is a place you just don't want to be in.

Fix the design with the choices noted in my prior reply.

Have fun. Received on Wed Jan 03 2007 - 16:31:48 CST