CODEWITHSUNDEEP
mysqlsqlforeign-keys
yuk
yuk

Reputation: 59

Sql Foreign Key Constraint Problems

Supposed there is table called 'Bill', In the Bill Table, there are 3 columns , ['id', 'client_id', ' client_contact_id'], only 'client_contact_id' is nullable. The client_id will foreign to the table of 'client', and in the table of 'client_contact', there are 3 columns such that ['id','name','client_id']. How do i supposed to make a integrity of these tables to make sure the data inserted in table 'bill' with 'client_id' is same as the client_contact_id of client_id.

I want to make integrity of bill.client_contact.client_id is same as bill.client_id in mysql.

I have to mentioned that the Client : Bill are 1:n , Client :Client_contact are 1:n also. Client_contact : Bill are 1:n enter image description here

Upvotes: 0

Views: 82

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use a foreign key constraint with two columns:

foreign key (client_id, client_contact_id) references client_contacts(client_id, id)

You will need a unique index/constraint on those columns:

create unique index client_contacts_2 on client_contacts(client_id, id);

This is a bit redundant, but solves your problem without using triggers or stored procedures. It also allows NULL values. These should be ignored for foreign key relationships.

Upvotes: 1

Shadow
Shadow

Reputation: 34294

If you cannot have bills without a contract, then I suggest you remove client id from the bills table.

If you can have bills without a contract, then you need to create before insert and before update triggers on the bills table. These triggers pull the client if from the client contact table if the client_contact_id field in the inserted / updated record is not null. Alternatively, you need to handle this in your application logic.

Upvotes: 0

Related Questions