I have the following tables:
PROFILE table {
user_id INT,
apitype enum('facebook' , 'main')
}
USER table {
id INT
}
PROFILE.user_id is a foreign key that points to the ID in USER table, but only if PROFILE.apitype = 'main'
so basically PROFILE.user_id does not refer to an ID in the USER table if PROFILE.apitype != 'main'
How can I accomplish this in MYSQL?
You can probably use a trigger which is fired before insert/update and checks the value of apitype. There're even some ways to throw errors in MySQL triggers, e.g. check this.
Well, as this is logic, it should be dealt with in the application, not the database.
You can add the user_id in the profile-table as nullable, making the connection optional, and depending on which storage engine you use you could try triggers (not knowing much about them in mysql though).
I think you cannot accomplish this in an easy way in Mysql.
For exemple in Postgres you have a check constraint, where you can check if apitype is main then user_id has to be null.
In Mysql you can only do this with trigger and it's not so easy. I think the easiest way for you and maybe the most reliable is to check it in your application. But you can still set a foreign key on user_id even if it's null sometimes
