0

Is there any method of constraining column types between tables in MariaDB? When creating foreign keys column types have to match. I would like to make sure two or more columns always stay the same type but without creating FK relationship.

Example:

I've got two tables:

  • players with position of player: x and y fields with type "TINYINT UNSIGNED"
  • npcs also with position of npc: x and y like above.

I want to prevent situation when npcs.x has different type than players.x (and same for y).

I would imagine that in some distant future somebody might want to change position in favor of something like SIGNED value. I want to be sure that those fields in two tables are still the same type so whenever there is a copy process (spawn npc on player location) data types in those fields are the same.

If there only existed user defined type functionality I will certainly mark those fields the same type here and there. For now I can only think of something like Polymorphic Association as some kind of probable solution here, but I haven't tried it yet.

0
2

I am unaware of a kind of constraint that could be used specifically for this purpose. I suspect there is none at the moment, happy to be proved wrong though. Still, if you would like to decrease the probability of a data type mismatch between some or other columns happening as a result of potential changes to the database (be it in a distant future or otherwise out of your control), you could try and employ the COMMENT option of a column definition in a CREATE TABLE/ALTER TABLE statement. This way you can leave instructions for (future) maintainers explaining that data types of certain columns should be kept identical, so that if one of the columns is changed, the other(s) should be changed likewise.

Of course, this is very far from a constraint solution that you are looking for. Not only is there no guarantee that the instructions will be followed, there is not even a guarantee they will be read. This is because the column comments can be viewed with the SHOW FULL COLUMNS command, but unless prompted, people are probably more likely to check out a table's schema using the shorter syntax, SHOW COLUMNS, which does not include the comments. The only guarantee you can have with the COMMENT method is that your instructions will be stored together with the schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.