CODEWITHSUNDEEP
mysqlsyntaxuniqueworkbench
Anthony Aslangul
Anthony Aslangul

Reputation: 3847

MySql Workbench syntax error

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

Upvotes: 0

Views: 2836

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

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

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

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.

Upvotes: 1

Related Questions