Referencing

[jemz]

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.

[r937]

yes, it's possible

[jemz]

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...

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));

[r937]

f_id in secondTable should ~not~ be auto_increment, even if id in firstTable is

[jemz]

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.

[r937]

..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

[jemz]

please show the INSERT statements you are using to insert into both tables

Hi this is my script in inserting the records.

 <?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();     
      }
      
?>

[r937]

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

[jemz]

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.?...

[r937]

check this -- http://dev.mysql.com/doc/refman/5.0/...unique-id.html

[jemz]

check this -- http://dev.mysql.com/doc/refman/5.0/...unique-id.html

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

[jemz]

check this -- http://dev.mysql.com/doc/refman/5.0/...unique-id.html

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