SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: Referencing

  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Referencing

    Hi,

    I need some advice is it possible to reference a primary key which is auto_increment?..I am using MySQL.

    Thank you in avdvance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, it's possible

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, it's possible


    Hi,But i tried to make table with one primary key which is auto_increment,and one foreign key auto_increment which is referencing to other tables primary key..but when i create the table it will generate error because it will not allowed to have 2 auto_increment...
    Code:
    create table secondTable( id int not null auto_increment,
                                        name varchar(30),
                                        address varchar(30),                              
                                        f_id int auto_increment,
                                        primary key(id),
                                        foreign key(f_id) referencing firstTable(id));

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    f_id in secondTable should ~not~ be auto_increment, even if id in firstTable is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    f_id in secondTable should ~not~ be auto_increment, even if id in firstTable is
    Hi, thank you for the reply...but if i don't make my f_id auto_increment the value of my f_id will be null,everytime i insert a record to the 2 tables.can you help me please how do i fixed this or what should i do...

    Thank you in advance.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    ..but if i don't make my f_id auto_increment the value of my f_id will be null,everytime i insert a record to the 2 tables.
    please show the INSERT statements you are using to insert into both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please show the INSERT statements you are using to insert into both tables

    Hi this is my script in inserting the records.

    PHP Code:
     <?php
       
         
    include_once("database.php");
         
         function 
    addTable1(  $id,
                                  
    $fld2,
                        
    $fld3,
                        
    $fld4,)
          {
          
             
    $sql "INSERT INTO table1 values(default,'$id','$fld1','$fld2','$fld3')";
         
    $res=mysql_query($sql);
             
             
    mysql_close();        
          } 
          
          
          function 
    addTable2($name,$description,$author)
          {
            
    $sql "insert into table2 values(default,
                                                   '
    $fld1',
                                   '
    $fld2',
                                   '
    $fld3',
                                   default)"
    ;
                                                      
          
    $result mysql_query($sql);
                                          
           
               
             
    mysql_close();     
          }
          
    ?>

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i don't do php but that script looks quite incomplete

    i thought your tables were called firstTable and secondTable, not table1 and table2 ???

    i don't see you using LAST_INSERT_ID() or mysql_insert_id() anywhere, which is what you need to use to retrieve the auto_increment value of the first table's insert, so that you can use it for the value of f_id in the second
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't do php but that script looks quite incomplete

    i thought your tables were called firstTable and secondTable, not table1 and table2 ???

    i don't see you using LAST_INSERT_ID() or mysql_insert_id() anywhere, which is what you need to use to retrieve the auto_increment value of the first table's insert, so that you can use it for the value of f_id in the second
    Hi, Thank you for the reply...Can you please show me what you mean i am confuse...How do i retrieve the auto_increment value for my first table then,so that i can insert it to the second table.?...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Hi,Thank you for this link..okay i will try this,I will write back to let you know if it is working.Thank you again

  12. #12
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post

    Hi, it's working i used the las_insert_id() ?..thank you so much for helping me.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •