How to enforce foreign key constraints in a self-referencing association?

Question

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.

Answer 1

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