1
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema motivian
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema motivian
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `motivian` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `motivian` ;

-- -----------------------------------------------------
-- Table `motivian`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `motivian`.`user` ;

CREATE TABLE IF NOT EXISTS `motivian`.`user` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NULL,
  `password` VARCHAR(100) NOT NULL,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
  PRIMARY KEY (`user_id`));


-- -----------------------------------------------------
-- Table `motivian`.`calculation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `motivian`.`calculation` ;

CREATE TABLE IF NOT EXISTS `motivian`.`calculation` (
  `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `calc` VARCHAR(100) NOT NULL,
  `user_id` INT NULL,
  `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY ()
    REFERENCES `motivian`.`user` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This is auto generated with mysql workbench but the constraint could not be applied. I tried like this:

CONSTRAINT user_id FOREIGN KEY (user_id)
        REFERENCESmotivian.user(user_id) ON DELETE NO ACTION ON UPDATE NO ACTION);

but also don't work.....The problem is with the constraint in the calculation table. How it should be written. This is the error message:

Executing SQL script in server
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 ')
    REFERENCES `motivian`.`user` ()
    ON DELETE NO ACTION
    ON UPDATE NO A' at line 8
SQL Code:
        CREATE TABLE IF NOT EXISTS `motivian`.`calculation` (
          `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
          `calc` VARCHAR(100) NOT NULL,
          `user_id` INT NULL,
          `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`category_id`),
          CONSTRAINT `user_id`
            FOREIGN KEY ()
            REFERENCES `motivian`.`user` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)

SQL script execution finished: statements: 8 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

the problem is with the constraint. And now I am thinking is it needed at all. I just want to have a relation with the users table but maybe the constraint is not needed when I create the table??

1
  • Do you get some error message? Can you add it?
    – jkavalik
    Jun 3, 2015 at 10:29

1 Answer 1

1

Your dump is somewhat weird, but your "repaired" part seems good (only missing one space, I suppose typo in question) - did it give different error? Column types do not match:

`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT NULL,

One is UNSIGNED and the other is not, that is one problem. Make them both either signed or unsigned (usually unsigned is the right one for autoincrement)

I just added missing space and some backticks to be safe:

CONSTRAINT `user_id` FOREIGN KEY (`user_id`)
    REFERENCES `motivian`.`user`(`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION);

Check it at http://sqlfiddle.com/#!9/9dca1/1

2
  • Yes this is the solution, thanks. But now I am asking myself if I need this constraint. I have many calculation for every user and when I am inserting new calculation I will point in the query for which exact account is every calculation. So if I delete this constraint actually nothing will change. (this is only for a insurance if someone put something else in this column different than user_id. Am I right? Jun 3, 2015 at 11:31
  • Well, yes, if your application handles it correctly, then this should not be needed. Constraints are for checking data consistency on DB side. If someone tries to delete user which has calculations, server won't let it happen. If you remove the reference, user can be deleted and you will then have orphaned calculations. Or you can change "NO ACTION" to "CASCADE" in "ON DELETE" and by simply deleting user, all their calculations will be deleted automatically.
    – jkavalik
    Jun 3, 2015 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.