Thanks, but I don't think a unique index is what I want. I need to allow any number of records with the same user_id and a value of 'N' in is_primary.
-----Original Message----- From: Gary Richardson [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:18 PM To: Josh Howe Cc: [EMAIL 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.. It basically says that each combination of user_id and is_primary needs to be unique. This won't work if you want to have multiple user_id = 5 and is_primary = n, for example. On Wed, 13 Oct 2004 15:45:56 -0400, Josh Howe <[EMAIL PROTECTED]> wrote: > > > 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
