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 or UPDATE. This implies that they had their own trigger for those
events. Triggers are not *yet* implemented in MySQL (see the TODO lists
for versions >=5 ) so I believe that you will need to enforce the "only 1
primary record" constraint in your application code until the server can
take over in some future version.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Josh Howe" <[EMAIL PROTECTED]> wrote on 10/13/2004 03:45:56 PM:
>
>
> 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 constraint on the table.
> Does MySQL have anything similar, or do I need to check the data in
> every place I do an insert into this table? Thanks.
>
>
>
>
>
