What is the best way to handle this constraint in SQL Server 2005?

Question

I have SMS based survey application which takes in a survey domain, and a answer.

I've gotten requests for detailed DDL, so.... The database looks like this

SurveyAnswer.Answer must be unique within all active Surveys for that SurveyDomain. In SQL terms, this should always return 0..1 rows:

select * from survey s, surveyanswer sa
where s.surveyid = sa.surveyid and
      s.active = 1 and
      s.surveydomainid = @surveydomainid
      sa.answer = @answer

I plan on handling this constraint at the application level, but would also like some database integrity to be enforced. What is the best way to do this? Trigger? Possible in a constraint?

Answer 1

As you are covering 2 tables there is AFAIK only 2 ways to enforce this.

1. Trigger as you suggested.
2. Indexed view with unique constraint accross the 3 columns.

As far as reliability is concerned I would go for the Indexed view but the only downside is that it will be difficult to understand by third parties.

Answer 2

Assuming you are using stored procedures to perform DML operations, you could add a guard clause to the SP that adds answers to surveys to check for the existence of an equivalent answer. You could then either throw an exception or return a status code to indicate that the answer could not be added.

Answer 3

It is possible to add a constraint that is implemented in a UDF like this:

alter table MyTable add constraint complexConstraint
check (dbo.complexConstraintFct()=0)

Where complexConstraintFct would be a function containing a query on other tables. However this approach has some issues as check constraints were designed to be evaluated on a single row at a time but updates can affect more that one row at a time.

So, the bottom line is: stick with triggers.

Answer 4

You can't do it at the row level (eg CHECK constraint) so you have to have something that can view all rows

A trigger can send "nice" messages, but they run after the DML statement. You have fine control over processing.

An indexed view prevents the DML statement, but it gives a technical error message. It's an extra object and indexes to maintain.

Answer 5

I think what you're saying is that for any active question, the tuple (surveyDomain, surveyQuestion, surveyAnswer) must be unique?

Or in other words, survey:surveyanswer is 1:1 if the survey is active, even though survey:surveyanswer is set up to be 1:many.

If so, the answer is to change your table structure. Adding a nullable activeAnswerId column to survey will effectively make the relation 1:1; your existing constraint unique SurveyId (or unique SurveyId, SurvetDomainId) will suffice to enforce uniqueness.

Indeed, unless I'm misunderstanding, I'm surprised that Survey has a Question column; I'd expect Survey:Question to be 1:many (a survey has many questions) or even many:many, if a question can show up on more than one survey.

More generally, I suspect the reason that figuring out how to enforce the constraint is difficult and requires "heroics" like triggers or user defined functions, is a symptom of a schema that doesn't accurately model your problem domain.

OP comments:

no, you're missing it. Survey:Answer is 1:n. "Question" is the survey question – Tuple would be (SurveyDomain.SurveyDomainId, Survey.Answer)

You mean that for every domain, there's at most one answer? Again, looking at your schema, it's misleading at best. A SurveyDomain has many Surveys (each of which has a Question column) and a Survey has many Answers? (Schema)

But if the Survey's active bit is set, there should be only one Answer?

Is Survey a misnomer for Question?

It's really not clear what you're trying to model.

Again, if it's hard to add a constraint, that suggests that your model doesn't work.