I don't get whats wrong with the mysql syntax

Question

    CREATE TABLE hoofdtoonder
  (
     id             INT NOT NULL,
     idondersoorten INT FOREIGN KEY REFERENCES `ondersoort`(`id`) NOT NULL,
  )

//making table but the error is with the references its on a mysql database someone please help

It says error at FOREIGN KEY REFERENCES ondersoort(id) NOT NULL. But I don't know what's wrong with the syntax.

Answer 1

MySQL does not support inline foreign key references.

It's true that the SQL language allows for syntax like @Mureinik suggested:

idondersoorten INT NOT NULL REFERENCES `ondersoort`(`id`) 

But you will find that MySQL parses this and ignores it. InnoDB does not support inline foreign key syntax. If you now run SHOW CREATE TABLE hoofdtoonder, it'll show this:

CREATE TABLE `hoofdtoonder` (
  `id` int(11) NOT NULL,
  `idondersoorten` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Where did the REFERENCES go? It was silently discarded. This is actually a beef I have with MySQL, that it recognizes some valid constraint syntax, but ignores it. It doesn't even show you a warning. It just defines the table without the constraint.

In MySQL, you must declare a foreign key as a table-level constraint, like this:

CREATE TABLE hoofdtoonder (
    id             INT NOT NULL,
    idondersoorten INT NOT NULL,
    FOREIGN KEY (idondersoorten) REFERENCES `ondersoort`(`id`)     
);

Answer 2

There are several things wrong here:

1. First, for an inline constraint, you don't need to specify foreign key, just references.
2. The not null clause should come before the references clause.
3. You have a redundant comma at the end of the last column's specification.

To put it all together:

CREATE TABLE hoofdtoonder (
    id             INT NOT NULL,
    idondersoorten INT NOT NULL REFERENCES `ondersoort`(`id`)     
);