MySQL Performance: MySQL => Duplicate Key Problem

Members

Help

Home

Home » Performance » MySQL » Duplicate Key Problem

Show: Today's Messages :: Show Polls :: Message Navigator

Duplicate Key Problem [message #693]

Thu, 08 February 2007 10:50

newtomysql
Messages: 30
Registered: February 2007

Member

Dear All,
I am quite new to innodb. My problem is a bit funny. I have a table which stores a serial and the status with Id as the primary key which I set. My problem is like this
ID Serial Status
1 12345 b
2 12345 b
3 12345 b
4 12345 b
5 12345 y

What I want to enable is that any time the combination of serial & status as 'b' can be duplicate and serial and status 'y' cannot be duplicate only one. How can I enforce this integrity in my datatabe. Thanks.

Report message to a moderator

Re: Duplicate Key Problem [message #694 is a reply to message #693 ]

Thu, 08 February 2007 16:52

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

You can't fix it simply by using unique keys. You will need to create trigger which will check on insert/update if there is already row matching your criteria.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Duplicate Key Problem [message #702 is a reply to message #694 ]

Fri, 09 February 2007 10:36

newtomysql
Messages: 30
Registered: February 2007

Member

Dear Peter,
So I have to build my trigger some thing like below rite. I not sure with the trigger syntax
CREATE TRIGGER test1 BEFORE INSERT ON table1
FOR EACH ROW BEGIN
If
Select ID from table1 where serial='12345' and status='y'
Else
// allow me to insert my new query
END;

My problem now is how to decide if previously it exist that is using a query as i stated above and also send the serial value ?
I have another solution is that before I do an insert in my program I will run through the database and see if it exist if yes then dont allow this new insert to happen. Which you think is much better the trigger or check before the database entry ? Thanks for your help.

Report message to a moderator

Previous Topic:	Join tables across database
Next Topic:	bad optimization or bad structure?

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 16:00:33 EDT 2009

Total time taken to generate the page: 0.01040 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.