Support creating foreign key constraints on the create table operation #79
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Allow references on columns in `CREATE TABLE` operations so that foreign keys can be created.
exekias
reviewed
Sep 5, 2023
| Unique bool `json:"unique"` | ||
| PrimaryKey bool `json:"pk"` | ||
| Default *string `json:"default"` | ||
| References *ColumnReference `json:"references"` |
There was a problem hiding this comment.
I wonder if we should make this part of the column definition or the table. The reason is that foreign keys can also be a constraint at the table level, and they can reference several columns instead of just one.
I guess we are fine with this for now and we can allow foreign key definition at the table level later? It would definitely be needed to support multi column foreign keys
There was a problem hiding this comment.
I've created #81 to track multi-column foreign keys.
exekias
approved these changes
Sep 5, 2023
andrew-farries
added a commit
that referenced
this pull request
Sep 6, 2023
#80) Allow the **add column** operation to create foreign key columns. An example of such an operation is: ```json { "name": "17_add_rating_column", "operations": [ { "add_column": { "table": "orders", "column": { "name": "user_id", "type": "integer", "references": { "table": "users", "column": "id", } } } } ] } ``` Most of the work to support the operation is in #79. * The constraint is added on `Start` (named according to the temporary name of the new column). * The entire new column, including the foreign key constraint, is removed on `Rollback`. * The constraint is renamed to use the final name of the new column on `Complete`. Test cases are included for both nullable and non-nullable FKs.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Allow creating foreign key columns when doing a create table operation. For example:
{ "name": "19_create_orders_table", "operations": [ { "create_table": { "name": "orders", "columns": [ { "name": "id", "type": "serial", "pk": true }, { "name": "user_id", "type": "integer", "references": { "table": "users", "column": "id" } }, { "name": "quantity", "type": "int" } ] } } ] }Here the
user_idcolumn references theidcolumn in theuserstable.The constraint is added to the table on
Startand removed onRollback.