Loopback-next: Database migration should create SQL constraints for relations

[bajtos]

_See https://github.com/strongloop/loopback-next/issues/1718 for background context._

As a user building an LB4 application with HasMany BelongsTo or HasOne relation, I want lb4 automigrate to define SQL constraints (foreign key, unique) in such way that referential integrity will be correctly enforced by the database.

Acceptance criteria

• [ ] Improve the base SQL connector loopback-connector to include CONSTRAINT definitions when updating tables.
• [ ] Find out how to test this functionality in automated way in our connectors. It's important to test both autoupdate (incremental changes to existing schema) and automigrate (creating new tables).

Ensure the new tests are executed as part of connector test suite and that they pass on our CI for the following connectors

• [ ] [loopback-connector-mysql](https://github.com/strongloop/loopback-connector-mysql)
• [ ] [loopback-connector-postgresql ](https://github.com/strongloop/loopback-connector-postgresql )
• [ ] [loopback-connector-mssql ](https://github.com/strongloop/loopback-connector-mssql )
• [ ] [loopback-connector-oracle ](https://github.com/strongloop/loopback-connector-oracle )

Depending on how much work the following connectors will require, either improve them as part of this story or create follow-up stories:

• [ ] [loopback-connector-db2iseries ](https://github.com/strongloop/loopback-connector-db2iseries )
• [ ] [loopback-connector-dashdb ](https://github.com/strongloop/loopback-connector-dashdb )
• [ ] [loopback-connector-informix ](https://github.com/strongloop/loopback-connector-informix )
• [ ] [loopback-connector-db2z ](https://github.com/strongloop/loopback-connector-db2z )

[nabdelgadir]

We'll do mysql for this story and create follow-up stories for the remaining ones, as it's quite large.

[Microtribute]

@bajtos @nabdelgadir I am able to create foreign key constraints by adding settings.foreignKeys configuration to a model. but the problem is loopback attempts to create foreign keys along with the table creation. A foreign key could reference a column of a table that is yet to be created. In this case, loopback migration script fails. Foreign key creation must be delayed until tables are all created. Here's an example of the error. That happened because the users table had not been created yet.

[klassicd]

@authenticode You may be interested in https://github.com/strongloop/loopback-connector/pull/170 which forces migrations to be run in a series so you can effectively use the models option to migrateShema as mentioned here https://loopback.io/doc/en/lb4/todo-list-tutorial-sqldb.html#database-migration-using-npm-run-migrate-command

[stale[bot]]

This issue has been marked stale because it has not seen activity within six months. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository. This issue will be closed within 30 days of being stale.