SQLAlchemy: check data existence in other table before insert data to not using foreign key

Question

I’m working on new project, and we decided to use Flask, SQLAlchemy, Mysql. I’m get used to using foreign key, however, for this time DBA told us we can’t use foreign key. So I would like to check if data exists in other table to keep data as clean and pure before insert or update data. I can write some code to check this out, but I would like to know If there is a way to check data existence on SQLAlchemy or flask, because any kinds of API using this table always have to check data existence and it is gonna be waste to implement same code for one reason.

Also, I want to know if this kinds of situation is common(restricted to use foreign key)

Answer 1

It's not entirely clear what is happening, but I'll take a guess.

If this new project is using a totally separate database, then it may not be possible to enforce a foreign key for a table in the original database. (That doesn't mean you can't have a column with the relevant id, just that you can't ensure the pointed-to row still exists.) MySQL does allow foreign keys in foreign databases, but there seem to be some constraints. If the databases are on separate machines, for example, this may not be available. In that case, you will need to have a second database connection/API call and use that to search in the original database to validate the new data before adding your objects. And before you use that id, you need to check if it still exists and handle the error if not.

But really, I think you need to go back to the DBA and ask him/her to explain why it's not possible. And then get this person to suggest an alternative.