New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't create UNIQUE constraints with column name of`group` in sqlite #4407

Closed
zuohaocheng opened this Issue Aug 31, 2015 · 0 comments

Comments

2 participants
@zuohaocheng

zuohaocheng commented Aug 31, 2015

I've created a table with a column, named group, and there's a UNIQUE constraint on it. It works on postgres, but will fail on sqlite. The SQL statement is

CREATE TABLE IF NOT EXISTS `Requests` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `group` INTEGER NOT NULL REFERENCES `Groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `license` INTEGER NOT NULL REFERENCES `Licenses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, UNIQUE (group, license));

However the correct SQL should be as below, which needs column names in UNIQUE to be quoted.

CREATE TABLE IF NOT EXISTS `Requests` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `group` INTEGER NOT NULL REFERENCES `Groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `license` INTEGER NOT NULL REFERENCES `Licenses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, UNIQUE (`group`, `license`));

May you help to fix the issue, thank you.

Table definition:

sequelize.define('Request', {
    group: {
      type: Sequelize.INTEGER,
      allowNull: false,
      unique: 'uGroupLicense'
    },
    license: {
      type: Sequelize.INTEGER,
      allowNull: false,
      unique: 'uGroupLicense'
    }
  }

@mickhansen mickhansen added the bug label Aug 31, 2015

mkaufmaner added a commit to mkaufmaner/sequelize that referenced this issue Apr 25, 2017

Add quotes around column names for unique constraints in sqlite (fixes
…sequelize#4407) (sequelize#7224)

* Add quotes around column names for unique constraints in sqlite (fixes sequelize#4407)

* Changelog linked to issue

holm added a commit to holm/sequelize that referenced this issue Nov 13, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment