Skip to content

Commit 43b6c35

Browse files
kanthoneybrianc
kanthoney
authored and
brianc
committed
Add table level foreign keys
* Add table level foreign keys * Make refColumns optional in foreign keys * Add constraint name to foreign keys * Add on update clause to foreign keys, and also to column references while I was at it * Add actions SET NULL, SET DEFAULT and NO ACTION for onDelete and onUpdate for foreign keys and column references
1 parent a43efac commit 43b6c35

4 files changed

Lines changed: 191 additions & 12 deletions

File tree

‎lib/dialect/postgres.js‎

Lines changed: 66 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -153,6 +153,7 @@ Postgres.prototype.visit = function(node) {
153153
case 'FOR SHARE' : return this.visitForShare();
154154
case 'TABLE' : return this.visitTable(node);
155155
case 'COLUMN' : return this.visitColumn(node);
156+
case 'FOREIGN KEY' : return this.visitForeignKey(node);
156157
case 'JOIN' : return this.visitJoin(node);
157158
case 'LITERAL' : return this.visitLiteral(node);
158159
case 'TEXT' : return node.text;
@@ -296,6 +297,7 @@ Postgres.prototype.visitCreate = function(create) {
296297
// don't auto-generate from clause
297298
var table = this._queryNode.table;
298299
var col_nodes = table.columns.map(function(col) { return col.toNode(); });
300+
var foreign_key_nodes = table.foreignKeys;
299301

300302
var result = ['CREATE TABLE'];
301303
if (create.options.isTemporary) result=['CREATE TEMPORARY TABLE'];
@@ -313,6 +315,9 @@ Postgres.prototype.visitCreate = function(create) {
313315
}.bind(this)).join(', ');
314316
colspec += ')';
315317
}
318+
if(foreign_key_nodes.length > 0) {
319+
colspec += ', ' + foreign_key_nodes.map(this.visit.bind(this)).join(', ');
320+
}
316321
colspec += ')';
317322
result.push(colspec);
318323
this._visitCreateCompoundPrimaryKey = false;
@@ -836,9 +841,14 @@ Postgres.prototype.visitColumn = function(columnNode) {
836841

837842
var onDelete = columnNode.references.onDelete;
838843
if (onDelete) onDelete = onDelete.toUpperCase();
839-
if (onDelete === 'CASCADE' || onDelete === 'RESTRICT') {
844+
if (onDelete === 'CASCADE' || onDelete === 'RESTRICT' || onDelete === 'SET NULL' || onDelete === 'SET DEFAULT' || onDelete === 'NO ACTION') {
840845
txt.push(' ON DELETE ' + onDelete);
841846
}
847+
var onUpdate = columnNode.references.onUpdate;
848+
if (onUpdate) onUpdate = onUpdate.toUpperCase();
849+
if (onUpdate === 'CASCADE' || onUpdate === 'RESTRICT' || onUpdate === 'SET NULL' || onUpdate === 'SET DEFAULT' || onUpdate === 'NO ACTION') {
850+
txt.push(' ON UPDATE ' + onUpdate);
851+
}
842852
var constraint = columnNode.references.constraint;
843853
if (constraint) {
844854
constraint = ' ' + constraint.toUpperCase();
@@ -850,6 +860,61 @@ Postgres.prototype.visitColumn = function(columnNode) {
850860
return [txt.join('')];
851861
};
852862

863+
Postgres.prototype.visitForeignKey = function(foreignKeyNode)
864+
{
865+
var txt = [];
866+
if(this._visitingCreate) {
867+
assert(foreignKeyNode.table, 'Foreign table missing for table reference');
868+
assert(foreignKeyNode.columns, 'Columns missing for table reference');
869+
if(foreignKeyNode.refColumns !== undefined) {
870+
assert.equal(foreignKeyNode.columns.length, foreignKeyNode.refColumns.length, 'Number of local columns and foreign columns differ in table reference');
871+
}
872+
if(foreignKeyNode.name !== undefined) {
873+
txt.push('CONSTRAINT ' + this.quote(foreignKeyNode.name) + ' ');
874+
}
875+
txt.push('FOREIGN KEY ( ');
876+
for(var i = 0; i < foreignKeyNode.columns.length; i++) {
877+
if(i>0) {
878+
txt.push(', ');
879+
}
880+
txt.push(this.quote(foreignKeyNode.columns[i]));
881+
}
882+
txt.push(' ) REFERENCES ');
883+
if(foreignKeyNode.schema !== undefined) {
884+
txt.push(this.quote(foreignKeyNode.schema) + '.');
885+
}
886+
txt.push(this.quote(foreignKeyNode.table));
887+
if(foreignKeyNode.refColumns !== undefined) {
888+
txt.push(' ( ');
889+
for(i = 0; i < foreignKeyNode.refColumns.length; i++) {
890+
if(i>0) {
891+
txt.push(', ');
892+
}
893+
txt.push(this.quote(foreignKeyNode.refColumns[i]));
894+
}
895+
txt.push(' )');
896+
}
897+
var onDelete = foreignKeyNode.onDelete;
898+
if(onDelete) {
899+
onDelete = onDelete.toUpperCase();
900+
if(onDelete === 'CASCADE' || onDelete === 'RESTRICT' || onDelete === 'SET NULL' || onDelete === 'SET DEFAULT' || onDelete === 'NO ACTION') {
901+
txt.push(' ON DELETE ' + onDelete);
902+
}
903+
}
904+
var onUpdate = foreignKeyNode.onUpdate;
905+
if(onUpdate) {
906+
onUpdate = onUpdate.toUpperCase();
907+
if(onUpdate === 'CASCADE' || onUpdate === 'RESTRICT' || onUpdate === 'SET NULL' || onUpdate === 'SET DEFAULT' || onUpdate === 'NO ACTION') {
908+
txt.push(' ON UPDATE ' + onUpdate);
909+
}
910+
}
911+
if(foreignKeyNode.constraint) {
912+
txt.push(' ' + foreignKeyNode.constraint.toUpperCase());
913+
}
914+
}
915+
return [txt.join('')];
916+
};
917+
853918
Postgres.prototype.visitFunctionCall = function(functionCall) {
854919
this._visitingFunctionCall = true;
855920
var txt = functionCall.name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';

‎lib/node/foreignKey.js‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
'use strict';
2+
3+
var Node = require(__dirname);
4+
5+
module.exports = Node.define({
6+
type: 'FOREIGN KEY',
7+
constructor: function(config) {
8+
Node.call(this);
9+
this.name = config.name;
10+
this.columns = config.columns;
11+
this.schema = config.schema;
12+
this.table = config.table;
13+
this.refColumns = config.refColumns;
14+
this.onUpdate = config.onUpdate;
15+
this.onDelete = config.onDelete;
16+
this.constraint = config.constraint;
17+
}
18+
});
19+

‎lib/table.js‎

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@ var TableNode = require(__dirname + '/node/table');
99
var JoinNode = require(__dirname + '/node/join');
1010
var LiteralNode = require(__dirname + '/node/literal');
1111
var Joiner = require(__dirname + '/joiner');
12+
var ForeignKeyNode = require(__dirname + '/node/foreignKey');
1213

1314
var Table = function(config) {
1415
this._schema = config.schema;
@@ -18,6 +19,7 @@ var Table = function(config) {
1819
this.isTemporary=!!config.isTemporary;
1920
this.snakeToCamel = !!config.snakeToCamel;
2021
this.columns = [];
22+
this.foreignKeys = [];
2123
this.table = this;
2224
if (!config.sql) {
2325
config.sql = require('./index');
@@ -45,6 +47,16 @@ Table.define = function(config) {
4547
for (var i = 0; i < config.columns.length; i++) {
4648
table.addColumn(config.columns[i]);
4749
}
50+
51+
if(config.foreignKeys !== undefined) {
52+
if(util.isArray(config.foreignKeys)) {
53+
for(i = 0; i < config.foreignKeys.length; i++) {
54+
table.foreignKeys.push(new ForeignKeyNode(config.foreignKeys[i]));
55+
}
56+
} else {
57+
table.foreignKeys.push(new ForeignKeyNode(config.foreignKeys));
58+
}
59+
}
4860
return table;
4961
};
5062

@@ -55,7 +67,8 @@ Table.prototype.clone = function(config) {
5567
sql: this.sql,
5668
columnWhiteList: !!this.columnWhiteList,
5769
snakeToCamel: !!this.snakeToCamel,
58-
columns: this.columns
70+
columns: this.columns,
71+
foreignKeys: this.foreignKeys
5972
}, config || {}));
6073
};
6174

‎test/dialects/create-table-tests.js‎

Lines changed: 92 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -256,25 +256,27 @@ Harness.test({
256256
dataType: 'int',
257257
references: {
258258
table: 'user',
259-
column: 'id'
259+
column: 'id',
260+
onDelete: 'restrict',
261+
onUpdate: 'set null'
260262
}
261263
}]
262264
}).create(),
263265
pg: {
264-
text : 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id"))',
265-
string: 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id"))'
266+
text : 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE SET NULL)',
267+
string: 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE SET NULL)'
266268
},
267269
sqlite: {
268-
text : 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id"))',
269-
string: 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id"))'
270+
text : 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE SET NULL)',
271+
string: 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE SET NULL)'
270272
},
271273
mysql: {
272-
text : 'CREATE TABLE `post` (`userId` int REFERENCES `user`(`id`))',
273-
string: 'CREATE TABLE `post` (`userId` int REFERENCES `user`(`id`))'
274+
text : 'CREATE TABLE `post` (`userId` int REFERENCES `user`(`id`) ON DELETE RESTRICT ON UPDATE SET NULL)',
275+
string: 'CREATE TABLE `post` (`userId` int REFERENCES `user`(`id`) ON DELETE RESTRICT ON UPDATE SET NULL)'
274276
},
275277
oracle: {
276-
text : 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id"))',
277-
string: 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id"))'
278+
text : 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE SET NULL)',
279+
string: 'CREATE TABLE "post" ("userId" int REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE SET NULL)'
278280
},
279281
params: []
280282
});
@@ -643,4 +645,84 @@ Harness.test({
643645
string: 'CREATE TABLE "post" ("id" int PRIMARY KEY)'
644646
},
645647
params: []
646-
});
648+
});
649+
650+
Harness.test({
651+
query: Table.define({
652+
name: 'post',
653+
columns: [{
654+
name: 'id',
655+
dataType: 'int',
656+
primaryKey: true
657+
}, {
658+
name: 'blog_id',
659+
dataType: 'int'
660+
}, {
661+
name: 'user_id',
662+
dataType: 'int'
663+
}],
664+
foreignKeys: {
665+
table: 'users',
666+
columns: [ 'blog_id', 'user_id' ],
667+
refColumns: [ 'id', 'user_id' ]
668+
}
669+
}).create(),
670+
pg: {
671+
text : 'CREATE TABLE "post" ("id" int PRIMARY KEY, "blog_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ( "id", "user_id" ))',
672+
string: 'CREATE TABLE "post" ("id" int PRIMARY KEY, "blog_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ( "id", "user_id" ))'
673+
},
674+
sqlite: {
675+
text : 'CREATE TABLE "post" ("id" int PRIMARY KEY, "blog_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ( "id", "user_id" ))',
676+
string: 'CREATE TABLE "post" ("id" int PRIMARY KEY, "blog_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ( "id", "user_id" ))'
677+
},
678+
mysql: {
679+
text : 'CREATE TABLE `post` (`id` int PRIMARY KEY, `blog_id` int, `user_id` int, FOREIGN KEY ( `blog_id`, `user_id` ) REFERENCES `users` ( `id`, `user_id` ))',
680+
string: 'CREATE TABLE `post` (`id` int PRIMARY KEY, `blog_id` int, `user_id` int, FOREIGN KEY ( `blog_id`, `user_id` ) REFERENCES `users` ( `id`, `user_id` ))'
681+
},
682+
params: []
683+
});
684+
685+
Harness.test({
686+
query: Table.define({
687+
name: 'replies',
688+
columns: [{
689+
name: 'id',
690+
dataType: 'int',
691+
primaryKey: true
692+
}, {
693+
name: 'blog_id',
694+
dataType: 'int'
695+
}, {
696+
name: 'post_id',
697+
dataType: 'int'
698+
}, {
699+
name: 'user_id',
700+
dataType: 'int'
701+
}],
702+
foreignKeys: [{
703+
table: 'users',
704+
columns: [ 'blog_id', 'user_id' ],
705+
onDelete: 'no action'
706+
}, {
707+
name: 'posts_idx',
708+
table: 'posts',
709+
columns: [ 'blog_id', 'post_id' ],
710+
refColumns: [ 'blog_id', 'id' ],
711+
onDelete: 'cascade',
712+
onUpdate: 'set default'
713+
}]
714+
}).create(),
715+
pg: {
716+
text : 'CREATE TABLE "replies" ("id" int PRIMARY KEY, "blog_id" int, "post_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ON DELETE NO ACTION, CONSTRAINT "posts_idx" FOREIGN KEY ( "blog_id", "post_id" ) REFERENCES "posts" ( "blog_id", "id" ) ON DELETE CASCADE ON UPDATE SET DEFAULT)',
717+
string: 'CREATE TABLE "replies" ("id" int PRIMARY KEY, "blog_id" int, "post_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ON DELETE NO ACTION, CONSTRAINT "posts_idx" FOREIGN KEY ( "blog_id", "post_id" ) REFERENCES "posts" ( "blog_id", "id" ) ON DELETE CASCADE ON UPDATE SET DEFAULT)'
718+
},
719+
sqlite: {
720+
text : 'CREATE TABLE "replies" ("id" int PRIMARY KEY, "blog_id" int, "post_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ON DELETE NO ACTION, CONSTRAINT "posts_idx" FOREIGN KEY ( "blog_id", "post_id" ) REFERENCES "posts" ( "blog_id", "id" ) ON DELETE CASCADE ON UPDATE SET DEFAULT)',
721+
string: 'CREATE TABLE "replies" ("id" int PRIMARY KEY, "blog_id" int, "post_id" int, "user_id" int, FOREIGN KEY ( "blog_id", "user_id" ) REFERENCES "users" ON DELETE NO ACTION, CONSTRAINT "posts_idx" FOREIGN KEY ( "blog_id", "post_id" ) REFERENCES "posts" ( "blog_id", "id" ) ON DELETE CASCADE ON UPDATE SET DEFAULT)'
722+
},
723+
mysql: {
724+
text : 'CREATE TABLE `replies` (`id` int PRIMARY KEY, `blog_id` int, `post_id` int, `user_id` int, FOREIGN KEY ( `blog_id`, `user_id` ) REFERENCES `users` ON DELETE NO ACTION, CONSTRAINT `posts_idx` FOREIGN KEY ( `blog_id`, `post_id` ) REFERENCES `posts` ( `blog_id`, `id` ) ON DELETE CASCADE ON UPDATE SET DEFAULT)',
725+
string: 'CREATE TABLE `replies` (`id` int PRIMARY KEY, `blog_id` int, `post_id` int, `user_id` int, FOREIGN KEY ( `blog_id`, `user_id` ) REFERENCES `users` ON DELETE NO ACTION, CONSTRAINT `posts_idx` FOREIGN KEY ( `blog_id`, `post_id` ) REFERENCES `posts` ( `blog_id`, `id` ) ON DELETE CASCADE ON UPDATE SET DEFAULT)'
726+
},
727+
params: []
728+
});

0 commit comments

Comments
 (0)