PHP and MySQL Foreign key not working as expected

Question

I'm doing a bit of MySQL using commands through PHP only (without using the phpMyAdmin interface to create the tables) and I'm having a bit of a problem creating Primary and Foreign keys. This is my current code.

<?php
$conectar = mysql_connect("localhost", "root", "") or die (mysql_error());
//mysql_query("CREATE DATABASE DataBaseTeste") or die(mysql_error());
mysql_select_db("DataBaseTeste") or die(mysql_error());
mysql_query("CREATE TABLE Pergunta("
    . "id_Pergunta INT AUTO_INCREMENT,"
    . "Descricao TEXT,"
    . "Nivel VARCHAR,"
    . "PRIMARY KEY(id_Pergunta),"
    . "FOREIGN KEY(id_Assunto) REFERENCES Assunto(id_Assunto))")Or die(mysql_error());

mysql_query("CREATE TABLE Aluno("
    . "id_Aluno INT NOT NULL,"
    . "AlunoNome CHAR,"
    . "Grupo CHAR,"
    . "PRIMARY KEY(id_Aluno))")Or die(mysql_error());

mysql_query("CREATE TABLE Assunto("
    . "id_Assunto INT,"
    . "Descricao VARCHAR,"
    . "PRIMARY KEY(id_Assunto))")Or die(mysql_error());


mysql_close();

?>

I also tried using this (without the FOREIGN KEY, by just inserting the other table's id):

$conectar = mysql_connect("localhost", "root", "") or die (mysql_error());
//mysql_query("CREATE DATABASE DataBaseTeste") or die(mysql_error());
mysql_select_db("DataBaseTeste") or die(mysql_error());
mysql_query("CREATE TABLE Pergunta("
    . "id_Pergunta INT AUTO_INCREMENT,"
    . "Descricao TEXT,"
    . "Nivel VARCHAR,"
    . "PRIMARY KEY(id_Pergunta),"
    . "id_Assunto INT)")Or die(mysql_error());

The error message I get is this:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY KEY(id_Pergunta)..."

It's probably something pretty stupid but I can't figure it out.

Answer 1

First, try to use lowercase names, I just did it for PKs

Then to use foreign keys you must add the column name do that table which will hold the FK

Also then, you need to use INNODB, can't remember if MyIsam allows you FK, but I recommend it, note that INNODB will create an index for them too.

CREATE TABLE assunto(
    id_assunto INT,
    descricao VARCHAR(254),
    PRIMARY KEY(id_assunto)
)ENGINE=INNODB; 

CREATE TABLE aluno(
    id_aluno INT NOT NULL,
    alunoNome CHAR(254),
    grupo CHAR,
    PRIMARY KEY(id_aluno)
)ENGINE=INNODB;


CREATE TABLE pergunta(
    id_pergunta INT AUTO_INCREMENT,
    id_assunto int,
    descricao TEXT,
    nivel VARCHAR(254),
    PRIMARY KEY(id_pergunta),
    FOREIGN KEY(id_assunto) REFERENCES assunto(id_assunto)
)ENGINE=INNODB;

Let me add you a PHP version, note the execution order.

<?php
$conectar = mysql_connect("localhost", "root", "") or die (mysql_error());
//mysql_query("CREATE DATABASE DataBaseTeste") or die(mysql_error());
mysql_select_db("DataBaseTeste") or die(mysql_error());

mysql_query("CREATE TABLE aluno("
    . "id_aluno INT NOT NULL,"
    . "alunoNome CHAR,"
    . "grupo CHAR,"
    . "PRIMARY KEY(id_aluno))")Or die(mysql_error());

mysql_query("CREATE TABLE assunto("
    . "id_assunto INT,"
    . "descricao VARCHAR,"
    . "PRIMARY KEY(id_assunto))")Or die(mysql_error());

    mysql_query("CREATE TABLE Pergunta("
    . "id_pergunta INT AUTO_INCREMENT,"
    . "id_assunto INT AUTO_INCREMENT,"
    . "descricao TEXT,"
    . "Nivel VARCHAR,"
    . "PRIMARY KEY(id_pergunta),"
    . "FOREIGN KEY(id_assunto) REFERENCES assunto(id_assunto))")Or die(mysql_error());

mysql_close();

This is a working sqlfiddle Mysql 5.1, I use 5.5.32 but show as 5.1 will work too. http://sqlfiddle.com/#!8/f88b5/3