CODEWITHSUNDEEP
phpmysqlsql
Chris Hansen
Chris Hansen

Reputation: 8685

foreign key mysql

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?

Upvotes: 1

Views: 177

Answers (3)

yokoloko
yokoloko

Reputation: 2860

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

Upvotes: 0

Eugene Yarmash
Eugene Yarmash

Reputation: 150138

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.

Upvotes: 1

Sgoettschkes
Sgoettschkes

Reputation: 13214

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

Upvotes: 0

Related Questions