How to add foreign key in sequelize cockroachdb

[karnprem90]

I have model like

let init = function() { 
return user_management = connection.define('user_management', {
            id: {
                type: DataTypes.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            email_id: {
                type: sequelize.STRING,
                allowNull: false
            },
            password: {
                type: sequelize.STRING
            },
            first_name: {
                type: sequelize.STRING,
                allowNull: false
            },
            middle_name: {
                type: sequelize.STRING,
                allowNull: true
            },
            last_name: {
                type: sequelize.STRING,
                allowNull: false
            },
            title: {
                type: sequelize.STRING,
                allowNull: false
            },
            company_name: {
                type: sequelize.STRING,
                allowNull: true
            },
            organisation_strength: {
                type: sequelize.STRING,
                allowNull: true
            },
            industry: {
                type: sequelize.STRING,
                allowNull: true
            },
            phone_number: {
                type: sequelize.INTEGER,
                allowNull: false
            },
            url: {
                type: sequelize.STRING,
                allowNull: true
            },
            personnel_number: {
                type: sequelize.INTEGER,
                allowNull: true
            },
}, 
          // I want to add unit id as foreign key in usermanagement table.
           classMethods: {
                associate: function(model) {
                    var usermanagement = model.user_management;
                    var unit = model.unit;
                    usermanagement.belongsTo(unit)
                },
})
}
module.exports = init

I am getting error while trying to create table
Unhandled rejection SequelizeDatabaseError: unimplemented at or near "null"
CREATE TABLE IF NOT EXISTS "user_managements" ("id" SERIAL , "email_id" VARCHAR(255) NOT NULL, "password" VARCHAR(255), "first_name" VARCHAR(255) NOT NULL, "middle_name" VARCHAR(255), "last_name" VARCHAR(255) NOT NULL, "title" VARCHAR(255) NOT NULL, "company_name" VARCHAR(255), "organisation_strength" VARCHAR(255), "industry" VARCHAR(255), "phone_number" INTEGER NOT NULL, "url" VARCHAR(255), "personnel_number" INTEGER, "user_role" VARCHAR(255) NOT NULL, "salt" VARCHAR(255), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "unitId" INTEGER REFERENCES "units" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id"));

[benesch]

That error is because we don't (yet) support ON DELETE SET NULL for foreign keys. (See e.g. cockroachdb/cockroach#14848 for ON DELETE CASCADE). To add a foreign key in Sequelize, you should be able to force RESTRICT behavior, which we do support, using something like:

usermanagement.belongsTo(unit, { onDelete: 'restrict', onUpdate: 'restrict' });

[michaelgmcd]

Could definitely be user error, but trying to create any sort of foreign key throws an error for me:
University.hasMany(Student, { onDelete: 'restrict' });

[CJTalbot87]

I have the same issue, using the suggested work around also fails.

Unhandled rejection SequelizeDatabaseError: unimplemented at or near "null"

Unhandled rejection SequelizeDatabaseError: unimplemented at or near "restrict"

[benesch]

@michaelgmcd, you need to specify both onDelete: 'restrict' and onUpdate: 'restrict', I'm afraid.

[CJTalbot87]

@benesch, same result:

Unhandled rejection SequelizeDatabaseError: unimplemented at or near "restrict"

[benesch]

@CJTalbot87, I'm afraid you'll need to share more details. Can you give us the model definitions and associations that don't work? Here's an example of a simple posts/comments association that does work: https://github.com/cockroachdb/sequelize-cockroachdb/pull/14/files#diff-803bac73fe9250752e76eef0d292d296R61

[CJTalbot87]

Sure, please find my code and results below. I'm running sequelize@3.30.4, sequelize-cockroachdb@1.0.2 and CCL v1.0.4 @ 2017/07/27 17:54:36 (go1.8.3) of CockroachDB:

var Sequelize = require('sequelize-cockroachdb');
var sequelize = new Sequelize('test', 'maxroach', '', {
    dialect: 'postgres',
    port: 26257
});


var Post = sequelize.define('post', { body: Sequelize.STRING });
var Comment = sequelize.define('comment', { body: Sequelize.STRING });
Post.hasOne(Comment, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT' });

sequelize.sync({ force: true }).then(function() {
    Post.create({
        body: 'Hello, world',
        comment: { body: 'Why, hello!' }
    }, { include: [Comment] });
}).then(function(post) {
    post.comment.update({ body: 'Goodbye!' });
});

Unhandled rejection SequelizeBaseError: unimplemented at or near "restrict"
CREATE TABLE IF NOT EXISTS "comments" ("id" SERIAL , "body" VARCHAR(255), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "postId" INTEGER REFERENCES "posts" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, PRIMARY KEY ("id"));
^

at Query.formatError (/root/wbe/node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
at Result.<anonymous> (/root/wbe/node_modules/sequelize/lib/dialects/postgres/query.js:88:19)
at emitOne (events.js:96:13)
at Result.emit (events.js:188:7)
at Result.Query.handleError (/root/wbe/node_modules/pg/lib/query.js:163:8)
at Client.<anonymous> (/root/wbe/node_modules/pg/lib/client.js:188:26)
at emitOne (events.js:96:13)
at Connection.emit (events.js:188:7)
at Socket.<anonymous> (/root/wbe/node_modules/pg/lib/connection.js:133:12)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:551:20)