constraint problem

[jesse]

Hi,

I have the following table

CREATE TABLE tbAnswers
(answer_id INTEGER NOT NULL PRIMARY KEY,
answer_correct BINARY NOT NULL,
answer_position INTEGER NOT NULL,
answer_text VARCHAR(150) NOT NULL,
item_id INTEGER NOT NULL REFERENCES tbItems(item_id) ON UPDATE CASCADE ON DELETE CASCADE,
UNIQUE(answer_position,item_id);

(actually I wanted a boolean for answer_correct but I had to do that by hand afterwards, anyone know how to do it in a create table statement?)


Now for the real problem: I have a constraint on it that says there should be minimum one correct answer per question, that one works fine. But I also want a constraint that says there can not be more than one correct answer per question. My first try was:

CONSTRAINT cstMax_one_correct_answer CHECK(UNIQUE(SELECT item_id FROM tbAnswers WHERE answer_correct=true))

which isn't accepted by access (also not without the "check"). My second try was:

CONSTRAINT cstMax_one_correct_answer CHECK((SELECT COUNT(*) FROM tbAnswers WHERE answer_correct=true)<=(SELECT COUNT(*) FROM (SELECT DISTINCT item_id FROM tbAnswers)))

which also doesn't work. Apparently I may not use distinct in a check clause. I'd really appreciate some help with this.

kind regards,
Jesse

 

[jdraw]

If you're using JET Access, CHECK is not supported.

Sample Create Table:
CREATE TABLE tblCustomers (
CustomerID INTEGER CONSTRAINT PK_tblCustomers
PRIMARY KEY,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50),
Address TEXT(40) DEFAULT Unknown)

samples of constraints are given here
http://msdn.microsoft.com/en-us/library/aa140011(v=office.10).aspx

 

[jesse]

No, that's not the case. I have a number of check constraints in my database that work just fine. For example the minimum one correct answer constraint on the same table is:

CONSTRAINT cstMin_one_correct_answer CHECK(item_id IN(SELECT item_id FROM tbAnswers WHERE answer_correct=true))

that one works perfectly fine.

The problem is that access apparently doesn't allow the DISTINCT keyword in a check clause and it also doesn't allow a query in a UNIQUE constraint.

With tose limitations I'm at a loss to write a proper constraint that specifies there is a maximum of one correct answer for each item_id. If anyone knwos how to do this within the access limitations I'd sure like to know.

 

[vbaInet]

Just wondering why you've chosen to boycott the Access interface to create tables?

I'm not sure about the specifics of using DISTINCT within a constraint but are you running this code via ADO or DAO?

Also, I would imagine that tbAnswers is the Many side of a one-to-many relationship between two tables. Can you not create a left join from the parent table to tbAnswers with an item_id criteria of Not Is Null?

As for the Boolean field, in Access you use YESNO.

 

[jesse]

thanks very much for thinking along.

In response to your questions: I'm more familiar with sql than with the access interface for creating tables and some of the people that are working on my project use sqlite so I'd like to have something that is reasonably portable. I'm using ADO (because I knew ADO before I got to access, I'm not very familiar with DAO)

You are correct in your assumption. The answer table is on the many side from the items items (which contains multiple choice questions).

I do not see how a join from the parent table would give me a constraint for maximum one correct answer, I can imagine this would work for a "minimum one correct answer" type of constraint, but I already have that in place.

What I want is a constraint that says that within the answer table there can only be one case of "answer_correct=true" for each item_id

 

[jesse]

Ah, but you did put me on the right track. I have found a solution:

CONSTRAINT cstMax_one_correct_answer CHECK(NOT EXISTS(SELECT item_id, answer_correct FROM tbAnswers WHERE answer_correct=true GROUP BY item_id,answer_correct HAVING COUNT(*)>1));

thanks very much!

 

[vbaInet]

In Access it's just drag and drop just like you have it in LAMPP for MySQL (if you know that enviroment). I think there are some limitations using DDL but I can't remember all of them. At least I know you won't be able to create fields with Access specific data types like the Attachment data type.

Re using the main table I didn't actually thing that through. You're right, it's not a viable solution. Maybe counting the Item_Ids and filtering out those greater than zero would have also worked but your approach is more optimum.

Glad you got it working.