PROTECTED]
Subject: Re: user defined table constraint
You need to use a UNIQUE index:
ALTER TABLE table ADD UNIQUE user_id_primary (user_id, is_primary);
I can't find a specific section about UNIQUE indexes in the mysql
docs, but I'm sure it's there and I'm pretty sure the syntax about is
correct
Hi all,
I have a table with these fields:
user_id
dept_id
is_primary ('Y' or 'N')
I want to make sure that there are never two rows in this table with the
same user_id and is_primary='Y'. For any user_id, there can only be one
primary record. In MS SQL I would define a user
That depends, can the user have more than 2 records? as in only 1 yes
record and 1 no record? If that were the case you could create a unique
index on (user_id, is_primary). However, I suspect that is not the case.
If I remember my M$ $QL correctly, User Constraints are evaluated during
INSERT
user_id
value, let alone the same user_id value and is_primary value. Wouldn't that
solve your problem without the need for a table constraint?
Rhino
- Original Message -
From: Josh Howe [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 3:45 PM
Subject: user defined