user defined table constraint

------_=_NextPart_001_01C4B15D.6C62FFDE
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=20

Hi all,

=20

I have a table with these fields:

=20

user_id

dept_id

is_primary ('Y' or 'N')

=20

I want to make sure that there are never two rows in this table with the
same user_id and is_primary=3D'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.=20

=20

=20


------_=_NextPart_001_01C4B15D.6C62FFDE--

Re: user defined table constraint

Excuse me for top-posting but Outlook Express won't put revision bars in
front of your original remarks and I'm too lazy to type them all in myself
;-)

Anyway, if you define one of your columns, such as user_id as a primary key,
you can be sure that there will never be two rows with the same 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"
To:
Sent: Wednesday, October 13, 2004 3:45 PM
Subject: user defined table constraint




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.







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: user defined table constraint

--=_alternative 006F71DA85256F2C_=
Content-Type: text/plain; charset="US-ASCII"

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" 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.
>
>
>
>
>

--=_alternative 006F71DA85256F2C_=--

Re: user defined table constraint

You need to use a UNIQUE index:

ALTER TABLE