Ensure integrity of columns with foreign key constraints applied

[kgodey]

Problem

Users may try to change the data type of columns with foreign key constraints applied, which may break the foreign key.

Solution

The API should not allow the data type of a column with a foreign key constraint to be changed.

[silentninja]

Is this meant to be a validation step?

[kgodey]

Is this meant to be a validation step?

I'm not sure what you mean.

[silentninja]

Is this meant to be a validation step?

I'm not sure what you mean.

If we change the datatype of a foreign key, I think it will cause an error, so I am not sure where this scenario would occur. Will it be a validation we do on the service layer before it gets to the database since the database won't allow this anyway

[mathemancer]

The API should not allow the data type of a column with a foreign key constraint to be changed.

It's not possible to break the link by altering column type. It's disallowed at the database level, and would return an error. There are some constrained situations where type alterations are allowed, but not in ways that create problems. For performance and flexibility, I suggest letting the user make the request, and presenting a "nice" version of the error if it's disallowed by the database. This would allow, for example, altering the foreign key column and the column it references to a BIGINT if there's danger of a rollover in a smooth way that doesn't risk breaking integrity. Otherwise, you need to drop the constraint, alter each column, then rebuild the constraint. This introduces the potential for corruption if not handled correctly (i.e., in a single transaction).

[kgodey]

@silentninja @mathemancer This is just a requirement from the product side, I did not consider whether the database allowed this or not when creating this ticket. If the DB doesn't allow it, handling this should just be creating the proper exception and error code and showing it in the API.

[mathemancer]

@kgodey Would you be okay with altering types whenever it wouldn't break the link? I.e., when it creates no error at the DB layer?

[kgodey]

@mathemancer Yup, that's fine.

[mathemancer]

In that case, I suggest just using the default DB behavior and handling errors gracefully.

[github-actions]

This issue has not been updated in 90 days and is being marked as stale.

[kgodey]

Closing this, too old, requirements are likely out of date. We can create a new issue if we need this functionality in the future.