• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using a trigger to ensure uniqueness

 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I want to ensure that rows inserted into a database table are unique based upon a set of keys (more than the 16 allowed by my version of mysql for PKs).
I am new to triggers and do not know how to implement a trigger that would perform such a check.
Can anyone please help? Code samples or links are welcome.
Thanks in advance,
Julien.

PS. Sorry my question is slightly off-topic but I do use Java for my app.
[ August 04, 2008: Message edited by: Julien Martin ]
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Julien,
I have no experience with "mysql", but I know of several databases that allow you to define a multi-column primary key for a database table and then the database server ensures that the primary key is unique.
So I believe you simply need to define a primary key.

Good Luck,
Avi.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Avi Abrami:
Julien,
I have no experience with "mysql", but I know of several databases that allow you to define a multi-column primary key for a database table and then the database server ensures that the primary key is unique.
So I believe you simply need to define a primary key.

Good Luck,
Avi.


MySQL has a 16 field limit on unique indices, hence Julien's predicament.

The trigger itself would be fairly basic, wouldn't it? It would need to be a before insert or update trigger that does a select for the count of rows based on the unique fields.

The trigger logic would need to run in a transaction to you could roll it back if the row already exists. I think this is the only way to prevent an insert/update via a trigger (someone else might know better)?

The MySQL manual has quite a lot about triggers. It should get you going.
[ August 05, 2008: Message edited by: Paul Sturrock ]
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot to all!
I solved the problem with the following trigger:

Regards,
Julien.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

CREATE TRIGGER pnr_transaction_tri BEFORE
INSERT

ON
pnr_transaction FOR EACH ROW

Aren't you forgetting UPDATES?
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oooops.... Yes you're right!
Thanks,
Julien.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic