Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – there can be only one auto column and it must be defined as a key

I’m trying to install this 4chan clone: https://github.com/Ro0ul/4Chan-Clone
I’m executing one of the queries on the sql folder on phpmyadmin:

CREATE TABLE comment(
    id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    post_id INT NOT NULL,
    posted_at DATETIME NOT NULL,
    image_src VARCHAR(255),
    board VARCHAR(50) NOT NULL,
    FOREIGN KEY(post_id)
        REFERENCES post(id)
        ON DELETE CASCADE
)

But I get the error: #1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key

How can I fix this without changing much the table (because like I said I want to install that 4chan clone)

Thanks in advance

Tags:

2

Answers


  1. 0

    You can define column id as PRIMARY KEY

    CREATE TABLE comment(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    post_id INT NOT NULL,
    posted_at DATETIME NOT NULL,
    image_src VARCHAR(255),
    board VARCHAR(50) NOT NULL,
    FOREIGN KEY(post_id)
        REFERENCES post(id)
        ON DELETE CASCADE
);

    Test it here

    Login or Signup to reply.
  2. 0

    From CREATE TABLE Statement

    There can be only one AUTO_INCREMENT column per table, it must be
    indexed, and it cannot have a DEFAULT value

    In simple terms AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. And it is a perfect candidate for PRIMARY KEY.

    How can I fix this without changing much the table (because like I
    said I want to install that 4chan clone)

    Add PRIMARY KEY after AUTO_INCREMENT

    Better would be

    CREATE TABLE comment(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    post_id INT NOT NULL,
    posted_at DATETIME NOT NULL,
    image_src VARCHAR(255),
    board VARCHAR(50) NOT NULL,
    FOREIGN KEY(post_id) REFERENCES post(id) ON DELETE CASCADE
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search