PDO creating a table with a foreign key

Question

I am having difficulty creating a table with MySQL (PDO) with a foreign key element, without the foreign key the table creates fine, but without I get this message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

I have tried searching for a solution and adapting the code, but seem to keep coming across this. Is there a fix or am I being a wally?

<?php
$servername = "localhost";
$username = "root";
$password = NULL;
$dbname = "testapplciants";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    //sql to create the activity registered table
    $sql = "CREATE TABLE Activity_Register (
    Activity_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    participant_id INT(6) FOREIGN KEY (participant_id) REFERENCES participants,
    entry_number INT(2),
    recorded_result INT(6),
    entry_date TIMESTAMP

    )";

    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Table Activity Recorder created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?> 

Answer 1

PRIMARY KEY in a column's definition is shorthand for a separate definition that reads PRIMARY KEY (`column_name`)

FOREIGN KEY has no such shorthand.

`participant_id` INT(6),
FOREIGN KEY (`participant_id`) REFERENCES `participants` (`id???`)

Note that you neglected the column name for the referenced table, and you should probably also have ON DELETE and ON UPDATE parameters too.