REFERENCE KEY in node-orm2

Question

I am working on REST API based on node.js and i chose postgresql database to store data. Suppose that this database has two tables names User and Comment. Any Comment belongs to One User and when we decide to remove an User, the Comment's of him/her must be removed. So, I designed my table as follows:

CREATE TABLE User(
    user_id SERIAL,
    username VARCHAR(32) NOT NULL,
    password VARCHAR(32) NOT NULL,
    CONSTRAINT pk_user PRIMARY KEY (user_id),
    CONSTRAINT uq_user UNIQUE (username)
)

CREATE TABLE Comment(
    comment_id SERIAL,
    user_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT pk_cmnt PRIMARY KEY (comment_id),
    CONSTRAINT fk_cmnt FOREIGN KEY (user_id) REFERENCES User(user_id)
        ON UPDATE CASCADE ON DELETE CASCADE
)

But i don't run this code and use node-orm2 instead. I designed two simple models to handle this simple code:

var User = db.define('user', {
    username: {
        type: 'text',
        size: 32, // VARCHAR(32)
        required: true, // NOT NULL
        unique: true // UNIQUE INDEX
    },
    password: {
        type: 'text',
        size: 32, // VARCHAR(32)
        required: true // NOT NULL
    }
}, {
    id: 'user_id' //SERIAL
});

var Cmnt = db.define('comment', {
    content: {
        type: 'text',
        required: true // NOT NULL
    }
}, {
    id: 'comment_id' //SERIAL
});

Cmnt.hasOne('user', User, {required: true}); // CREATE ASSOCIATION KEY

and synchronize database with these models :

db.sync();

Now, I want to insert new comment belongs to user which user_id doesn't exist. So, the Comment model accepts this and insert the row into comment table.

My question is, how can i do some things like REFERENCE KEY and the ON UPDATE CASCADE ON DELETE CASCADE ?

Thanks in advance :)

score 1 · Answer 1 · answered Aug 19 '13 at 09:34

Try to use deferrable like this:

CREATE TABLE Comment(
    comment_id SERIAL,
    user_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT pk_cmnt PRIMARY KEY (comment_id),
    CONSTRAINT fk_cmnt FOREIGN KEY (user_id) REFERENCES User(user_id)
        ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
)

REFERENCE KEY in node-orm2

1 Answers1