Foreign Key Question

amrigo

I do create two tables :
Table A :
id auto_increment
txt

Table B :
B_id
txt
id

Using the syntax i found in the manual :

CREATE TABLE parent(
id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE SET NULL
) TYPE=INNODB;

So i could not use auto_increment to B_id
because mysql says i can not have two auto fields ????

How can i increment the B_id field using php/mysql functions i think a
mysql function will work is that right?

Below is the sql i execute:

CREATE TABLE tableA(
cod_A INT NOT NULL AUTO_INCREMENT,
txt_assunto VARCHAR ( 255 ) NOT NULL
PRIMARY KEY (cod_A)
) TYPE=INNODB;
CREATE TABLE tableB (
cod_B INT NOT NULL,
link VARCHAR( 255 ) NOT NULL,
txt_desc TEXT,
cod_A INT,
INDEX c_a ( cod_A ),
FOREIGN KEY (cod_A) REFERENCES tableA(cod_A)
ON DELETE SET NULL
) TYPE=INNODB;

I paste the sql to code critique

Thank´s in advance

bastien

you can't have 2 auto increment fields IN THE SAME TABLE, each table can have an auto increment field.

amrigo

this code must works???

CREATE TABLE tableA(
cod_A INT NOT NULL,
txt_assunto VARCHAR ( 255 ) NOT NULL
PRIMARY KEY (cod_A)
) TYPE=INNODB;
CREATE TABLE tableB (
cod_B INT NOT NULL,
link VARCHAR( 255 ) NOT NULL,
txt_desc TEXT,
cod_A INT,
INDEX c_a ( cod_A ),
FOREIGN KEY (cod_A) REFERENCES tableA(cod_A)
ON DELETE SET NULL
) TYPE=INNODB;

amrigo

I think my question could be :

How can create foreign keys using phpmyadmin?

How can create an index using phpmyadmin?

How can create both using phpmyadmin?

Thank´s in advance

bastien

PHPMyAdmin can create an index with the gui...open it up and there is a set of icons to the right of the column name...third one in is the index (hover over it to see the text) and click to create the index...

To create FKs, you need to use the ALTER statement...read the manual www.mysql.com

calua

are you sure about the ALTER being needed to create FKs? It seems that setting fields as FKs when you create the table (as in the code he passed) is fine... 😕

amrigo

i did not find the place where i can set the foreign key in phpmyadmin do you already?

if so where?

i do it in the sql query window using only "references" so it create a foregin key ...

bastien

Originally posted by calua
are you sure about the ALTER being needed to create FKs? It seems that setting fields as FKs when you create the table (as in the code he passed) is fine... 😕

yes, unless he wantst o do it after the create table statement