Foreign Keys in multiple tables.

[Dragonkai]

I think I'm doing this wrong, I have 5 tables.

This is how I created my tables:


require('php/mysqldata.php');

mysql_query("CREATE TABLE subjects(
id INT NOT NULL,
PRIMARY KEY(id),
ethnic VARCHAR(30),
gender VARCHAR(10),
age INT,
public INT) ENGINE=INNODB")
or die(mysql_error());

mysql_query("CREATE TABLE nowaves(
id INT NOT NULL,
INDEX (id),
FOREIGN KEY (id) REFERENCES subjects (id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id),
q1 INT,
q2 INT,
q3 INT,
q4 INT,
q5 INT,
q6 INT,
q7 INT,
q8 INT,
q9 INT,
q10 INT) ENGINE=INNODB")
or die(mysql_error());

mysql_query("CREATE TABLE theta(
id INT NOT NULL,
INDEX (id),
FOREIGN KEY (id) REFERENCES subjects (id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id),
q1 INT,
q2 INT,
q3 INT,
q4 INT,
q5 INT,
q6 INT,
q7 INT,
q8 INT,
q9 INT,
q10 INT) ENGINE=INNODB")
or die(mysql_error());

mysql_query("CREATE TABLE alpha(
id INT NOT NULL,
INDEX (id),
FOREIGN KEY (id) REFERENCES subjects (id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id),
q1 INT,
q2 INT,
q3 INT,
q4 INT,
q5 INT,
q6 INT,
q7 INT,
q8 INT,
q9 INT,
q10 INT) ENGINE=INNODB")
or die(mysql_error());

mysql_query("CREATE TABLE beta(
id INT NOT NULL,
INDEX (id),
FOREIGN KEY (id) REFERENCES subjects (id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id),
q1 INT,
q2 INT,
q3 INT,
q4 INT,
q5 INT,
q6 INT,
q7 INT,
q8 INT,
q9 INT,
q10 INT) ENGINE=INNODB")
or die(mysql_error());



In which the 4 table's id all reference the subjects table' id.

When I tried to Insert something in subjects then into theta, it worked, but when I tried inserting something into alpha it said the foreign key already existed.

I don't know what I'm doing wrong, but basically I want all the other 4 tables to reference to the id that subjects has.

Thanks for any help.

[chazzy]

show us the insert statements you used.

[Dragonkai]

Oh I think I know what I did, I used the same table name for inserting.

However I am confused, since many tutorials use an id for each different table. I am guessing that is for many to many relationships, and for my case I am doing an one to many relationship and that means every table can have a foreign key straight to the primary table and does not need a id for every other table there is?

[chazzy]

well, your 'id' column should never be a FK. ideally, it's a generated value that is immutable - it represents the value at that row forever.

[Dragonkai]

Oh, I read in another tutorial that for this case, it isn't needed for the id to be a PK