foreign key mysql

Question

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?

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

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.

edited May 23 '17 at 12:31

Community

1
1

answered Aug 05 '11 at 11:45

Eugene Yarmash

142,882
41
325
378

score 0 · Answer 2 · answered Aug 05 '11 at 11:44

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).

score 0 · Answer 3 · answered Aug 05 '11 at 11:45

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

foreign key mysql

3 Answers3