You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version-CodePudding

I used the following code to create a table on MySQL:

CREATE TABLE IF NOT EXISTS auth (
  id INT AUTO_INCREMENT,
  email VARCHAR(50) UNIQUE NOT NULL PRIMERY KEY,
  password VARCHAR(250) NOT NULL,
  username VARCHAR(50) UNIQUE,
  admin BOOLEAN NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

But I got this error:

ERROR 1064 (42000): 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 'PRIMERY KEY,
  password VARCHAR(250) NOT NULL,
  username VARCHAR(50) UNIQUE,

What is the problem?

EDIT: The problem was because of a typo in PRIMARY KEY, but after fixing that, I faced another error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CodePudding user response：

If I'm not mistaken you typo'd PRIMERY KEY so it should be PRIMARY KEY

CREATE TABLE IF NOT EXISTS auth (
  id INT AUTO_INCREMENT,
  email VARCHAR(50) UNIQUE NOT NULL PRIMARY KEY,
  password VARCHAR(250) NOT NULL,
  username VARCHAR(50) UNIQUE,
  admin BOOLEAN NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

EDIT: The issue you are getting is because you have an auto_increment and primary key on different columns. You can use both on 1 column but not on 2 different ones because there can only be 1 auto increment per table. New script:

CREATE TABLE IF NOT EXISTS auth (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(250) NOT NULL,
  username VARCHAR(50) UNIQUE,
  admin BOOLEAN NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CodePudding user response：

The typo of PRIMERY is an obvious misspelling of PRIMARY. This is something you should have been able to answer yourself.

The second issue is that an auto-increment column must be the first column of a key. Most people say it must be the primary key, but technically it can be any key of the table.

The following table definition works:

CREATE TABLE IF NOT EXISTS auth (
  id INT UNIQUE AUTO_INCREMENT,
  email VARCHAR(50) PRIMARY KEY,
  password VARCHAR(250) NOT NULL,
  username VARCHAR(50) UNIQUE,
  admin BOOLEAN NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Adding UNIQUE to the id column makes it a key, which satisfies the requirement that the auto-increment column be the first column of a key.

I removed the UNIQUE NOT NULL keywords on the email column, because that's implied by being PRIMARY KEY. It's redundant, and unfortunately MySQL will unfortunately create an extra unnecessary unique index for email if you include both keywords.