relational database

[arctushar]

Hello
I m not so expert in web design. I need ur help.
I have two table. One has a primary key and other foreign key. I want that foreign key must be from primary key. other than primary key it will not except any value. I made relationship from phpmyadmin. but evan I can put value in foreign key cell, which is not in primary key. I made it in MS Access. Is it possible in mysql?
if possible then plz help me. Remember-- I want to do that through PHP or PHPMYADMIN

[hielo]

If the tables already exists, make sure that:
-BOTH are using INNODB engine.
-In both tables, the fields are of the same type and size ( ex : INT(10))

then try:

ALTER TABLE `childTableNameHere` 
	ADD CONSTRAINT `parentTableNameHerechildTableNameHere` 
	FOREIGN KEY (`parentTableNameHere_primaryFieldNameHere` )
	REFERENCES `parentTableNameHere`( `primaryFieldNameHere` )

[arctushar]

thanks. I think will help me. Can u plz explain what is different between normal engine and INNODB

[arctushar]

its still not working

[hielo]

Can u plz explain what is different between normal engine and INNODB

In MySQL, foreign key constraints will only work on INNODB and BDB engines. The online manual explains the engines in details.

If your tables already have data, and the existing data violate the integrity contraints, then the changes you tried to make will not work.

Suggestion: make a test parent and child table and try again. Assuming the parent table has PRIMARY KEY id INT(10), then the child table MUST have data type INT(10) as well. IT cannot be INT(20) [or any other number other than 10]

[arctushar]

In MySQL, foreign key constraints will only work on INNODB and BDB engines. The online manual explains the engines in details.

If your tables already have data, and the existing data violate the integrity contraints, then the changes you tried to make will not work.

Suggestion: make a test parent and child table and try again. Assuming the parent table has PRIMARY KEY id INT(10), then the child table MUST have data type INT(10) as well. IT cannot be INT(20) [or any other number other than 10]

I have done this. in phpmyading it work. but when I instert data through my own php code. foreign key field is taking also value which is not in primary key.

[hielo]

Did you try to create as couple of NEW parent an child tables and test it?

If the integrity contraints between Table A and Table B fail in your PHP script it MUST fail in PHPMyAdmin. Likewise,
If the integrity contraints between Table A and Table B succeed in PHPMyAdmin, it MUST succeed via your own PHP script.

The only way your statement (that it works in one but not in the other) makes sense to me is if in PHPMyAdmin you are testing Table A with Table B, but in your script you are testing Table C with Table D!