如何在自引用关联中强制执行外键约束? [英] How to enforce foreign key constraints in a self-referencing association?

问题描述

假设最简单的例子:

var Person = sequelize.define('Person', {名称:Sequelize.STRING，});Person.hasMany(Person, { as: 'Children', foreignKeyConstraint: true });

如果我们 sequelize.sync 这个，我们得到一个 ChildrenPersons 连接表，它有一个两列主键，由 PersonId 和ChildrenId，但没有外键:

创建表`ChildrenPersons`(`PersonId` int(11) NOT NULL DEFAULT '0',`ChildrenId` int(11) NOT NULL DEFAULT '0',`createdAt` 日期时间不为空，`updatedAt` 日期时间不为空，主键(`PersonId`、`ChildrenId`)) 引擎=InnoDB 默认字符集=latin1

如何制作 PersonId 和 ChildrenId CONSTRAIN ed 外键，引用 Persons.id ?

我尝试了手动创建连接表，但是没有用.

解决方案

我对 sequelize 不是很熟悉，但我知道你可以使用它直接发出 SQL 查询和使用 sequelize.query

因此，如果您只想创建带有外键约束的两个表，这里是合适的 SQL(请注意，这是 SQLite 方言 - 注意缺少日期时间)

CREATE TABLE Persons(id 整数主键，名称文本)；CREATE TABLE ChildrenPersons(personId 整数不为空，childrenId 整数不为空，createdAt 整数不为空，updatedAt 整数不为空，主键(personId，childrenId)，外键(personId)引用人(id)，外键(childrenId)引用人(id))；

希望能帮到你

编辑

以下代码将使用 sequelize lingo 创建表(已确认使用 sequelize 1.7.9):

var Sequelize = require('sequelize'),sequelize = new Sequelize('test','','',{方言:'sqlite'，存储:'test.db'})var Person = sequelize.define('Person', {name: Sequelize.STRING})var PersonChildren = sequelize.define('PersonChildren',{人员 ID:{类型:Sequelize.INTEGER，参考资料:人，参考密钥:'id'，主键:真},儿童 ID:{类型:Sequelize.INTEGER，参考资料:人，参考密钥:'id'，主键:真}})sequelize.sync({force: true}).complete(function(err){如果(！！错误){控制台日志('错误:'，错误)}别的{console.log('一切正常')}})

参考以下 Sequelize 文档文章来强制执行外键约束:http://sequelizejs.com/docs/latest/associations

Assuming the most simple example:

var Person = sequelize.define('Person', {
  name: Sequelize.STRING,
});

Person.hasMany(Person, { as: 'Children', foreignKeyConstraint: true });

If we sequelize.sync this, we get a ChildrenPersons join table that has a two-column primary key, formed by PersonId and ChildrenId, but no foreign keys:

CREATE TABLE `ChildrenPersons` (
  `PersonId` int(11) NOT NULL DEFAULT '0',
  `ChildrenId` int(11) NOT NULL DEFAULT '0',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`PersonId`,`ChildrenId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How can I make PersonId and ChildrenId CONSTRAINed foreign keys, referencing Persons.id ?

I tried manually creating the join table, but it didn't work.

解决方案

I am not really familiar with sequelize but I know you can use it to directly issue SQL queries and commands using sequelize.query

So if you just want to create the two tables with the foreign key constraints here is the appropriate SQL (Please note that this is SQLite dialect - notice the lack of datetime)

CREATE TABLE Persons(
    id integer primary key, 
    name text);
CREATE TABLE ChildrenPersons(
    personId integer not null, 
    childrenId integer not null, 
    createdAt integer not null,  
    updatedAt integer not null, 
    primary key(personId,childrenId), 
    foreign key(personId) references Persons(id), 
    foreign key(childrenId) references Persons(id));

I hope this helps

EDIT

The following code will create the table using sequelize lingo (Confirmed to be working with sequelize 1.7.9) :

var Sequelize = require('sequelize'),
sequelize = new Sequelize('test','','',
            {
            dialect: 'sqlite',
                storage: 'test.db'
            })
var Person = sequelize.define('Person', {name: Sequelize.STRING})
var PersonChildren = sequelize.define('PersonChildren',
        {
            PersonId: {
                type: Sequelize.INTEGER,
                references: Person,
                referencesKey: 'id',
                primaryKey: true
            },
            ChildrenId: {
                type: Sequelize.INTEGER,
                references: Person,             
                referencesKey: 'id',
                primaryKey: true
            }
        })
sequelize.sync({force: true}).complete(function(err){
    if(!!err){
        console.log('Error: ',err)
    }else
    {
        console.log('All OK')
    }   
})

Loot at he following Sequelize documentation article for enforcing foreign key constraints: http://sequelizejs.com/docs/latest/associations

这篇关于如何在自引用关联中强制执行外键约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！