MySql Workbench syntax error

Question

EDIT : Please read the problem before you post your answer :( I know UNIQUE shouldn't be here. I use MySQL Workbench to organize my database and, for an unknown reason, it decided to put UNIQUE here since this morning. That's the problem.

When i create 10 tables with many relationships, i synchronize my database with the diagram from MySql Workbench. Since i have some "UNIQUE" there and there, i can't really synchronize anything because i got errors. And copy pasting all the statements into notepad to debug them is not an option :p

The question is: why MySql Workbench is putting all these wrong UNIQUE in the create table statements ?

Thank you for your help :)

I have a weird error when i try to synchronize my model with the database.

The create table statement makes an error:

CREATE TABLE IF NOT EXISTS `barometres`.`brm_questions` ( 
`id` INT(10) UNIQUE UNSIGNED NOT NULL AUTO_INCREMENT, 
`theme_id` INT(10) UNIQUE UNSIGNED NOT NULL, 
`question_type_id` TINYINT(3) NOT NULL, 
`type` TINYINT(1) NULL DEFAULT NULL, 
`reference` TINYINT(1) NULL DEFAULT NULL, 
`created_at` DATETIME NOT NULL, 
`updated_at` DATETIME NULL DEFAULT NULL, 
`deleted_at` DATETIME NULL DEFAULT NULL, 
INDEX `fk_brm_questions_brm_themes1_idx` (`theme_id` ASC), 
INDEX `fk_brm_questions_brn_questions-types1_idx` (`question_type_id` ASC), 
PRIMARY KEY (`id`), 
CONSTRAINT `fk_brm_questions_brm_themes1` 
FOREIGN KEY (`theme_id`) 
REFERENCES `barometres`.`brm_themes` (`id`) 
ON DELETE NO ACTION 
ON UPDATE NO ACTION, 
CONSTRAINT `fk_brm_questions_brn_questions-types1` 
FOREIGN KEY (`question_type_id`) 
REFERENCES `barometres`.`brn_questions-types` (`id`) 
ON DELETE NO ACTION 
ON UPDATE NO ACTION) 
ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8

This is normal, the id column should not be marked as UNIQUE (since i didn't check it in Workbench), and the UNSIGNED should be before the UNIQUE anyway.

Because the UNSIGNED is after the UNIQUE, i got this error for each table in my model:

ERROR: Error 1064: 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 'UNSIGNED NOT NULL AUTO_INCREMENT, 
`theme_id` INT(10) UNIQUE UNSIGNED NOT NULL,' at line 2

As you can see, the theme_id column is also marked as UNIQUE even if i didn't check it too.

All my tables have the same problem and i really don't know how to solve it without manually change all statements in notepad.

Do you have any idea why workbench has this behavior ? This is Workbench 6.3.9, and the same model do the same error on 6.3.6

Thank you very much for your help.

Anthony

You can't use UNIQUE in the column definition. Instead you create a unique index on that column after the column specs. — Sloan Thrasher
– Sloan Thrasher, Commented Feb 13, 2017 at 13:06

Ravinder Reddy · Accepted Answer · 2017-02-13 13:06:55Z

1

You are wrongly using UNIQUE keyword. It should be used at the end of column definition.

Change:

`id` INT(10) UNIQUE UNSIGNED NOT NULL AUTO_INCREMENT

To:

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT -- UNIQUE keyword is removed

OR:

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

Note:
You don't need UNIQUE option explicit when you are defining a field as PRIMARY.
Because PRIMARY by default is UNIQUE. Hence as the field id is PRIMARY KEY on it, stop using UNIQUE on it.

answered Feb 13, 2017 at 13:06

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sloan Thrasher · Accepted Answer · 2017-02-13 13:11:08Z

To make a column unique, create a unique index on it. The Unique keyword shouldn't be in the column spec. Also, by making the id column the primary key, it is also unique.

Try this instead:

CREATE TABLE IF NOT EXISTS `barometres`.`brm_questions` ( 
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    `theme_id` INT(10) UNSIGNED NOT NULL, 
    `question_type_id` TINYINT(3) NOT NULL, 
    `type` TINYINT(1) NULL DEFAULT NULL, 
    `reference` TINYINT(1) NULL DEFAULT NULL, 
    `created_at` DATETIME NOT NULL, 
    `updated_at` DATETIME NULL DEFAULT NULL, 
    `deleted_at` DATETIME NULL DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    INDEX `fk_brm_questions_brm_themes1_idx` (`theme_id` ASC), 
    INDEX `fk_brm_questions_brm_questions-types1_idx` (`question_type_id` ASC), 
    UNIQUE INDEX `fk_brm_questions_brm_theme_id` (`theme_id`),
    CONSTRAINT `fk_brm_questions_brm_themes1` 
        FOREIGN KEY (`theme_id`) 
        REFERENCES `barometres`.`brm_themes` (`id`) 
        ON DELETE NO ACTION 
        ON UPDATE NO ACTION, 
    CONSTRAINT `fk_brm_questions_brn_questions-types1` 
        FOREIGN KEY (`question_type_id`) 
        REFERENCES `barometres`.`brn_questions-types` (`id`) 
        ON DELETE NO ACTION 
        ON UPDATE NO ACTION
) 
ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8

Collectives™ on Stack Overflow

MySql Workbench syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related