Question 7980 in PHP Versions Question posted in
You can find a list of all versions here.

why does my foreign key code show a syntax error? – PHP Versions

I’m attempting to create a table in a relational database that has a foreign key and a SQL syntax error consistently occurs.

I am using PHP and mySQL (i’ve removed any non interacting tables that only hold data and don’t contribute to the relations)

CREATE table IF NOT EXISTS logins( 
        loginID int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        buildingID int FOREIGN KEY REFERENCES buildings(buildingID))

buildings is another table, which has the primary key "buildingID"

the error that shows is

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘FOREIGN KEY REFERENCES buildings(buildingID), login varchar(255)…’

this error doesn’t happen when the foreign key is removed, the table creates with no errors; and I create this table after i create the table that the foreign key references.

i have tried using the other syntax:

CREATE table IF NOT EXISTS logins( 
        loginID int UNSIGNED AUTO_INCREMENT,
        buildingID int,
        PRIMARY KEY(loginID),
        FOREIGN KEY (buildingID) REFERENCES buildings(buildingID))

to no avail: the same error happens

what is my mistake here, and how can I change my code to fix it? Thanks.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    the error is that primary keys are not permitted to be unsigned, i changed this one thing in my code and the error went away


  2. 0

    Few typos.

    CREATE table IF NOT EXISTS logins( 
        loginID int UNSIGNED AUTO_INCREMENT ,
        buildingID int,
        PRIMARY KEY(loginID),
        FOREIGN KEY (buildingID) REFERENCES buildings(buildingID))

    this works fine

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search