1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

insert foreign keys with trigger

Discussion in 'Databases' started by k4bras, Jul 21, 2016.

  1. #1
    Hello.
    I got the following tables :


    CREATE TABLE A (
    id_a INT AUTO_INCREMENT PRIMARY KEY,
    name_a VARCHAR(50),
    surname_a VARCHAR(50));

    CREATE TABLE B(
    id_b INT AUTO_INCREMENT PRIMARY KEY,
    name_b VARCHAR(50),
    surname_b VARCHAR(50),
    unique bbb(name_b,surname_b));

    CREATE TABLE C(
    id_c INT AUTO_INCREMENT PRIMARY KEY,
    cab VARCHAR(40) UNIQUE);

    CREATE TABLE D(
    id_as INT,
    id_bs INT,
    id_cs INT,
    dt DATETIME,
    FOREIGN KEY(id_as) REFERENCES A(id_a),
    FOREIGN KEY(id_bs) REFERENCES B(id_b),
    FOREIGN KEY(id_cs) REFERENCES C(id_c));

    CREATE TABLE E(
    dat DATE,
    ora TIME,
    name_b VARCHAR(50),
    surname_b VARCHAR(50),
    name_a VARCHAR(50),
    surname_a VARCHAR(50),
    cab VARCHAR(40));


    Table A and table E are populated with a load file process. I am using a trigger on table E to populate tables B, C and D. The trigger has a continue handler for duplicate entries. I managed so far to populate tables B and C but I have REAL trouble on doing the same with table D. I can't find a way to get the primary keys into that table. I've tried to get them into a variable by using the handler's duplicate flag but is not working. Any ideas ? Any help would be highly appreciated. Thank you.
     
    Last edited: Jul 21, 2016
    k4bras, Jul 21, 2016 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    I get a little bit confused, but are all these tables necessary? I'm just wondering what the purpose of the tables are. You seem to keep the same values in multiple locations (unless I'm misunderstanding and there is one table for something uploaded, and another for downloaded or something like that?) which is never a good idea. Also, keeping names (first and last) to varchar(50) seems to me to be a possible pitfall. Yes, there aren't that many people using their full name if it's over 100 characters total, but sometimes people have multiple first names, for instance, and that can quickly pass 50 characters. Not a big concern, just a potential problem.
     
    PoPSiCLe, Jul 21, 2016 IP
  3. k4bras

    k4bras Peon

    Messages:
    7
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    3
    #3
    Thank you for your post. This is a project I am working on thus all these tables are necessary. This is not the exact structure of the tables ( column names and length) because its already a long post and I didn't want to make it even longer but to be precise : table A contains a list of doctors, table B a list of patients table C a list of cabinets table D a list with visit days and hours and the foreign key and table E has doctors, patients, cabinets and the visit information. I've been wraping my head for 2 days trying to solve this foreign key problem but I just can't do it.
     
    Last edited: Jul 21, 2016
    k4bras, Jul 21, 2016 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    Okay, so it's basically a many-to-many database.

    However, if the structure is as you has stated, it could probably be minimized a bit.

    Table A:
    Doctor information
    id, name, surname

    Table B:
    Patient information
    id, name, surname (why do you have the complete name here?)

    Table C:
    Cabinet information
    id, cab

    Table D:
    The visit table - I'm not sure why you're making this so complicated?
    id, doctor, patient, cabinet and datetime
    populated by auto-increase primary key, doctor_id, patient_id, cabinet_id and datetime

    Table E:
    id, datetime, doctor, patient,cabinet
    populated by auto-increase primary key, datetime from table d, doctor_id, patient_id and cabinet_id
    (however, that last table could be skipped completely, and basically just getting the information based on a joins when pulling the data)

    I think that would be both quicker and easier, since you're just using the IDs from the other tables, and you're not storing data multiple times - you will, of course, when pulling from Table E, have to do a join on the other tables to pull the information you need from those, so you actually get the names, not just an id. Joins ARE more costly, they are, but unless you're planning on pulling thousands of records, this shouldn't really be that big a problem?

    And what is the need for the foreign key restraints in Table D?
     
    PoPSiCLe, Jul 21, 2016 IP
  5. k4bras

    k4bras Peon

    Messages:
    7
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    3
    #5
    I can not change anything except delete table E after the other tables are populated.
     
    k4bras, Jul 22, 2016 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #6
    How does the trigger look that you currently have? The problem is probably that you do not have direct access to the IDs you need to populate the foreign key fields from Table E (where the trigger is located, if I'm not mistaken) - what you need is a multilayer trigger, which inputs the data into the other tables, then pulls those NEW.id_X and puts them in the foreign keys columns.
     
    PoPSiCLe, Jul 22, 2016 IP
  7. k4bras

    k4bras Peon

    Messages:
    7
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    3
    #7
    thank you for not losing interest for my problem. tomorrow is my deadline. the trigger looks like this:
    delimiter &&
    
    
    CREATE trigger tabint after insert on E
       
    for each row
    begin
       
    declare x int default 0;
       
    declare y,z int(5);
       
    declare continue handler for 1062
           
    begin
               
        set x=1;
       
    end;
           
           
    insert into B(name_b, surname_b) select new.name_b, new.surname_b;   
    if x=1 then set y=(select id_b from B);       
    else set y=(last_insert_id());     
    end if;       
    insert into C(cab) select new.cab;       
    if x=1 then set z=(select id_c from C);   
    else set z=(last_insert_id());    
    end if;
    end&&
    Code (SQL):
    it may not be corect...i'm really new at MySQL
     
    k4bras, Jul 22, 2016 IP
  8. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #8
    Well, that trigger is not complete.
    You'll have to insert the actual values you need in Table D as well?
    And you'll need to pull the last insert id - currently, all you're doing is pull all the ids from the different tables you've inserted into, I think

    Basically, after both of those inserts, when you have values for both x and y just do a new insert into the last table? Shouldn't that work?
     
    PoPSiCLe, Jul 22, 2016 IP
  9. k4bras

    k4bras Peon

    Messages:
    7
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    3
    #9
    Well, this trigger is not good because I cant insert a result set into a variable(y) . But I think this is part of the solution, using the handler's variable(x).
    When x = 1 it means that there is a duplicate entry and the only way to get the id is with select, when x = 0 it means that there is not a duplicate entry and I should be able to get the id with last_insert_id(). I don't know how to write it. At this point this is to much for me.
    I know the trigger is not complet but I don't know how to complet it :).
     
    k4bras, Jul 22, 2016 IP
  10. k4bras

    k4bras Peon

    Messages:
    7
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    3
    #10
    is this problem hard ? 408 views and no answer :)
     
    k4bras, Aug 2, 2016 IP
  11. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #11
    lol, possibly, but we know that if @PoPSiCLe has tried to help you and failed that we're unlikely to do any better!
     
    sarahk, Aug 2, 2016 IP
  12. k4bras

    k4bras Peon

    Messages:
    7
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    3
    #12
    I finally got it. I don't know if you guys are interested but here it goes :
    DELIMITER &&
    
    CREATE trigger mytrigger before insert on E for each row
    
    begin
    
    declare x,y,z,double int;
    declare dou_reg condition for 1062;
    
    declare continue handler for dou_reg 
      begin
    set double = 0;
      end;
    
    set double = 1;
    
      set foreign_key_checks = 0;
    
    insert into A(name_a, surname_a) values(new.name_a, new.surname_a);
    
    set x= last_insert_id();
    
        if double = 0 then
           select distinct id_a from A where name_a=new.name_a and surname_a=new.surname_a into x;
         set double = 1;
        end if;
    
     
    insert into B(name_b, surname_b) values (new.name_b, new.surname_b);
    
    set y= last_insert_id();
    
        if double = 0 then
           select distinct id_b from B where name_b=new.name_b and surname_b=new.surname_b into y;
         set double = 1;
        end if;
    
    insert into C (cab) values (new.cab);
    
    set z= last_insert_id();
    
         if double = 0 then
            select distinct id_c from C where cab=new.cab into z;
           set double = 1;
         end if;
    
    insert into D (id_as, id_bs, id_cs, dt) values (x,y,z,(concat(dat,' ',ora)));
    
    SET FOREIGN_KEY_CHECKS=1;
    
    end&&
    Code (markup):
     
    k4bras, Aug 8, 2016 IP
    sarahk and PoPSiCLe like this.