Support for Multi-Column Foreign Keys

[ondras]

From [email protected] on October 15, 2010 15:42:08

It seems wwwsqldesigner is only implementing column constraint logic for foreign keys, but not table constraint logic and is thus only allowing single column constraints for foreign keys.

Support for Multi-Column Foreign Keys is missing.

Many databases support multicolumn primary keys. Foreign keys usually refer to a primary (or unique) key of a table, hence need to contain the same number and type of columns of the referenced key (for unique or single column primary keys, this is then a single column constraint).

ORACLE:

All foreign key columns referenced in constraint must be NOT NULL.

For definition of foreign keys in oracle DB, see http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/clauses002.htm#SQLRF01111 example: CONSTRAINT FK_EXAMPLE_1 FOREIGN KEY (column1, column2, column3) REFERENCES TABLE_1 ( column_x, column_y, column_z);

POSTGRESQL: =========== http://www.postgresql.org/docs/9.0/static/sql-createtable.html CONSTRAINT FK_PGSQL_EXAMPLE_1 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

For postgresql it is possible to have some parts of the FK to be null depending on the MATCH type.

MYSQL:

(innodb). See: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html MSSQL:

Table constraint. See Create Table syntax: http://msdn.microsoft.com/en-us/library/ms174979.aspx

Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=110

[ondras]

From ondrej.zara on October 15, 2010 06:56:02

Status: Accepted
Owner: ondrej.zara

[ianshepherd]

Just a comment to say I've just run into this issue. I will have to manually edit the SQL output from wwwsqldesigner for the pair of tables I have with a compound PK/FK relationship for now

[libTorrentUser]

Yep. So far this is the only really important missing feature / bug I found.

Other than that, thanks for this kick-ass modeling tool.

[libTorrentUser]

Hi,

I've created a quick fix for this issue and now the foreign keys are generated correctly. Basically I created a new function inside io.js that will generate the correct composite FK SQL right after the application finishes generating the SQL using the output.xsl file.

For this patch to work you must comment out the FK generation inside the output.xsl file, otherwise you will see duplicated ALTER TABLE ... commands at the end of the generated SQL command.

This patch should work with any DB, since it outputs the FKs in the most conformant way possible. But I must warn you that I didn't test with anything other than MSSQL.

I cannot generate a patch from where I currently am, so I'll just attach the modified io.js file here. Those interested in this fix can simply copy it over the existing one (and don't forget to comment out the FK generation inside the output.xls!!!)

If you wanna know how it was done, just search for clientsql_fk inside io.js. I created this new function and then added a call to it at the end of SQL.IO.prototype.finish

Hope it helps!

io.zip

[nelson6e65]

Thanks, @libTorrentUser

I'm not actively using this, so I can't test it at the moment. But nice share! :tada: I'll post here if I get time to test it.