CODEWITHSUNDEEP
phpmysqlsqlforeign-key-relationship
Pranavadurai
Pranavadurai

Reputation: 1030

How to define FOREIGN KEY constraint in mysql in my case not working

I need to know about how to declare foreign key in mysql and how it works. Here's one sample first table contains name, age the second table refer the first tables name. While I run this, I receive error only.

<?php
$conn=new mysqli("localhost","root","12345");
$sql="USE new";
$conn->query($sql);
$sql="CREATE TABLE test(name varchar(20),age integer)";
$conn->query($sql);
$sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test     (name)";
if($conn->query($sql)==true)
{
    header('Locaton:test3.html');
}
else
{
    echo "error";
}
?>

Can anyone help me?

Upvotes: 3

Views: 2794

Answers (4)

i&#39;m PosSible
i&#39;m PosSible

Reputation: 1393

foreign key must refrence from a Primary key then use a primary key on name,

$sql="CREATE TABLE test(name varchar(20) PRIMARY KEY,age integer)";

and also change on 

$sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test(name))";

if your name field have chance to duplicate then take one another field id and use it to reference.

Upvotes: 1

Jaykumar Patel
Jaykumar Patel

Reputation: 27614

FOREIGN KEY constraint defined at the Column level

Query

   $sql="CREATE TABLE test2(name varchar(10) FOREIGN KEY REFERENCES test(name))";

FOREIGN KEY constraint defined at the Table level

Query

   $sql="CREATE TABLE test2(
                name varchar(10), 
                FOREIGN KEY (name) REFERENCES test(name)
   )";

Check this reference URL. Both way you can do.

Upvotes: 1

vhu
vhu

Reputation: 12798

Please note that you must use InnoDB when using foreign keys. Unless you have InnoDB as default, you need to specify the table type when creating the table.

CREATE TABLE test(name varchar(20) primary key,age integer) engine=innodb;
CREATE TABLE test2(name varchar(10) primary key,FOREIGN KEY (name) REFERENCES test     (name)) engine=innodb;

Also note that the you foreign keys need to be referencing indexed columns. Using primary key is one way to achieve this.

Upvotes: 1

user3248346
user3248346

Reputation:

You are missing a ) parenthesis at the end

$sql="CREATE TABLE test2(name varchar(10),FOREIGN KEY (name) REFERENCES test (name))";

Upvotes: 2

Related Questions