表创建中php代码中的外键约束

Question

I have a table named property structured like this:

$sql = "CREATE TABLE property (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            seller_id INT(6) NOT NULL,
            property_name VARCHAR(30) NOT NULL,
            address VARCHAR(30) NOT NULL,
            reg_date TIMESTAMP
            )";
            mysql_query($sql);

and I have another table named dealtype like this:

$sql = "CREATE TABLE dealtype (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            seller_id INT(6) NOT NULL,
            deal_type VARCHAR(30) NOT NULL,
            FOREIGN KEY (property_id) REFERENCES property (id),
            reg_date TIMESTAMP
            )";
            mysql_query($sql);

Now the property_id in the dealtype table is the foreign key of the id in the property table but its not working in the php code. The property table is created but the dealtype doesn't get created.

Where is my mistake?

Answer 1

You need to declare the variable with its type before specifying that it is a foreign key:

$sql = "CREATE TABLE dealtype (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            seller_id INT(6) NOT NULL,
            deal_type VARCHAR(30) NOT NULL,
            property_id int(6) unsigned,
            reg_date TIMESTAMP,
            FOREIGN KEY (property_id) REFERENCES property (id)
        )";

Presumably, seller_id has a similar relationship to a sellers table (or something similar).