Integrity constraint in MySQL to express "must have one primary attribute"

Question

I have a 2-table schema which can be specified as follows:

CREATE TABLE MEMBERS
( membershipnumber int NOT NULL PRIMARY KEY,
firstname varchar(20) NOT NULL,
lastname varchar(20) NOT NULL,
email varchar(30) NOT NULL,
status varchar(15) NOT NULL DEFAULT 'unapproved',
);

CREATE TABLE TELEPHONENUMBERS
( telephone varchar(15) NOT NULL PRIMARY KEY,
membershipnumber INT NOT NULL REFERENCES MEMBERS(membershipnumber),
isprimary enum('0','1') NOT NULL DEFAULT '1'
);

I am trying to specify an integrity constraint to indicate that any individual member may have only one primary telephone number (i.e. telephonenumbers.isprimary='1') and any number of secondary telephone numbers.

Here is my current attempt:

ALTER TABLE MEMBERS
ADD CONSTRAINT oneprimary_ck CHECK (SELECT COUNT(isprimary)=1 FROM TELEPHONENUMBERS WHERE TELEPHONENUMBERS.membershipnumber = membershipnumber) IN '1';

However this yields the following syntax error:

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(isprimary)=0 FROM TELEPHONENUMBERS WHERE TELEPHONENUMBERS.membershipnumber=1 IN '1') IN ''

I'm assuming my approach is totally wrong, but am unsure how to proceed. Would it be better to do this using a trigger or is there a small alteration ot my approach that would work?

I am using server version: 5.5.30.

With many thanks,

Froskoy.

Answer 1

Does it work any better if you swap ...

ALTER TABLE MEMBERS ADD CONSTRAINT oneprimary_ck CHECK
(SELECT COUNT(isprimary)=1 FROM TELEPHONENUMBERS
 WHERE TELEPHONENUMBERS.membershipnumber = membershipnumber) IN '1';

... with ...

ALTER TABLE MEMBERS ADD CONSTRAINT oneprimary_ck CHECK
(SELECT COUNT(isprimary) FROM TELEPHONENUMBERS
 WHERE isprimary=1 AND TELEPHONENUMBERS.membershipnumber = membershipnumber) > 1;

... ?

Answer 2

Two things are wrong in your approach:

1. A check constraint cannot contain a subquery
2. Check constraints are not enforced by MySQL (not in 5.5 anyway)

What you can do, is create a trigger that raises an error. Here's an example:

https://stackoverflow.com/a/7189396/417194