在我的情况下如何在mysql中定义FOREIGN KEY约束不起作用

Question

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?

Answer 1

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.