•  

Announcement

Announcement Module
Collapse
No announcement yet.

Duplicate Key Problem

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Duplicate Key Problem

    #1
    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.
    Tags: None

  • #2
    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.

    Comment


    • #3
      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.

      Comment

      Working...
      X