Referencing

Databases
1

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

yes, it’s possible

:slight_smile:

3

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

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

5

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

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

7

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

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

9

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

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

11

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

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