constraining one correct answer but multiple invalid answers

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? Received on Wed Jan 03 2007 - 15:55:24 CST