CODEWITHSUNDEEP
mysqlprimary-key
Ashish Saxena
Ashish Saxena

Reputation: 426

applying primary key to multiple columns in mysql

I have 3 Columns :

-> person(varchar(50))
-> related_with(varchar(50))
-> relationship(varchar(50))

The combination of above 3 is a primary key

Now for example values are given to 3 columns as : 

person -> User
related_with -> User1
relationship -> Son of

The problem is that if i add values as 

person -> User1
related_with -> User
relationship -> Son of

Then it is inserted successfully .. but this should not be the case since vice versa in this case cant be true

Is there a solution for this problem ??

Upvotes: 0

Views: 187

Answers (2)

nvogel
nvogel

Reputation: 25526

Make (person, related_with) the key. Mutual relationships like "brother" are implicit so you don't necessarily need two rows to represent them. Now you can add a CHECK constraint:

ALTER TABLE tbl ADD CONSTRAINT ck1 CHECK (person < related_with);

That's not a perfect solution for every kind of relationship but it does reduce the amount of redundancy and number of potential anomalies and it enforces the rules declaratively rather than as procedural code in a trigger.

Upvotes: 1

Husain Basrawala
Husain Basrawala

Reputation: 1751

Most of the times, such validations should be made at the application code level. However, if you must do it in database, consider writing a trigger.

This is indeed a complex logic and the validation (either in application or trigger). There can be various cases: 1. Consider a relationship -> friend of. Here a reverse relationship holds true unlike son of.

  1. Consider a relationship -> brother of. In this case, a reverse relation is possible if both the users are male, but not if User is female. Ditto for relationship -> sister of.

Hence you need to carefully device a way how you store look up to your relationships and if you want to hard the logic or use flags to identify type of relationship.

Upvotes: 1

Related Questions