sequelize: Error when creating non-association constraint on table in different schema

Dialect: mysql

When specifying a foreign key constraint that references a table in another schema, the correct format is:

FOREIGN KEY (tableId) REFERENCES Schema.Table (id) ON DELETE CASCADE ON UPDATE CASCADE

whereas what is generated is:

FOREIGN KEY (tableId) REFERENCES Table (id) ON DELETE CASCADE ON UPDATE CASCADE

(the difference is (a) the absence of the Schema name and (b) the presence of the tick marks)

This results even if specifying the model in the reference map, i.e.

references: { model: Schema.Table }

I have played around with other documented variations of the “references” option, but all produce this latter result, which fails to factor in the schema, and adds the tick marks which need to be removed.

Unless, of course, there exists a correct way to specify this relationship, that I’m missing…

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 26 (14 by maintainers)

Most upvoted comments

`Schema.Table`

is incorrect format in MySql. It should be either

Schema.Table

`Schema`.`Table`

Askelkana on Dec 16, 2015

Ok, some progress: by adding a ‘schema’ option when defining a Model, the schema appears in the association, and no references map is required. The sole problem now is that the quotes are not correct, i.e. we get

`Schema.Table`

when we should be getting:

`Schema`.`Table`

The quoteTable method is not being used, although the logic seems to be just what is wanted. I’m still digging to see where the string is constructed. Once found, the fix should be straightforward.

Addendum 1: It would appear that the MysqlDialect.prototype.supports object is missing the schema property, rendering it false. This causes the above quote problem in the quoteTablemethod.

However, setting it to true causes schemas to be injected throughout, and not necessarily as desired. For example, the implictly created tables in the belongsToMany scenario now may take the wrong schema, and so have to be defined explicitly to prevent this. This is not desirable behaviour, naturally. I am not familiar with the code base enough to know how best to fix this, even though a workaround exists.

Addendum 2: the workaround to the problem of “superfluous” and “erroneous” schema being added is to sync the schema before making any cross-schema associations. Once that is done, the associations can be added and the implicitly created tables (and any other affected tables) synced separately.

Askelkana on Dec 16, 2015