# Re: many to many link table compound primary key explosion

**URL:** <https://www.ruby-forum.com/t/re-many-to-many-link-table-compound-primary-key-explosion/89672>\
**Category:** Rails\
**Created:** [January 5, 2006, 6:04am UTC](https://www.ruby-forum.com/t/re-many-to-many-link-table-compound-primary-key-explosion/89672 "2006-01-05T06:04:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kevben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/90ced4/32.png) [@kevben](https://www.ruby-forum.com/u/kevben)\
**Post date:** [January 5, 2006, 6:04am UTC](https://www.ruby-forum.com/t/re-many-to-many-link-table-compound-primary-key-explosion/89672/1 "2006-01-05T06:04:28Z")

</div>

I think to begin with, an appropriate change to your data model would  
be:

create table answers\_questions (  
id int not null autoincrement,  
question\_id int unsigned not null,  
answer\_id int unsigned not null,  
constraint fk\_qid foreign key (question\_id) references questions(id),  
constraint fk\_aid foreign key (answer\_id) references answers(id),  
primary key (id)  
) ENGINE=InnoDB;

In general, it’s considered better practice to have primary keys be  
‘meaningless’ and not use them to implement what essentially is a  
business  
rule.

You can then implement uniqueness on the two foreign keys directly in  
active  
record using the built in validation capabilities it provides to ensure  
that  
you don’t get duplicates.

For info on validation, see:

```
 http://api.rubyonrails.com/classes/ActiveRecord/Validations.html

```

You have a choice to put the validation logic in the database or put it  
in the  
ActiveRecord models. There are reasons to go either way, but in this  
case  
pulling the valdation out of the database and putting it in the  
ActiveRecord  
models looks like it may be easier nd give you more flexibility.

–  
Kevin B.  
[http://www.kbedell.com](http://www.kbedell.com)

“The future belongs to those who believe in the beauty of their dreams.”

- Eleanor Roosevelt

----- End forwarded message -----

–  
Kevin B.  
[http://www.kbedell.com](http://www.kbedell.com)

“The future belongs to those who believe in the beauty of their dreams.”

- Eleanor Roosevelt

---

<div class="post-metadata">

**Author:** ![kevben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/90ced4/32.png) [@kevben](https://www.ruby-forum.com/u/kevben)\
**Post date:** [January 5, 2006, 10:38am UTC](https://www.ruby-forum.com/t/re-many-to-many-link-table-compound-primary-key-explosion/89672/2 "2006-01-05T10:38:34Z")

</div>

Hi Kevin,

thanks for the advice.

I don’t believe changing my data model is the right thing here. For a  
start it’s already helped me identify this potential problem 🙂

Also, those rows are already unique by the combination of answer\_id and  
question\_id. Adding a new primary key called id is meaningless.

An additional problem is that ActiveRecord includes all columns from the  
link table when joining the linked table, so the id field would collide  
(the link table id would take preference as I understand).

Technically, the primary keys in this instance aren’t implementing a  
business rule. The link table should never include duplicate link  
records. At the end of the day, I’m trying to do something that  
violates this rule, which is wrong. Perhaps the best course of action  
is just to figure out a graceful way to handle it in my code. I don’t  
know how to override the @question.answers.push method, if this were to  
be the best option.

John.  
[http://johnleach.co.uk](http://johnleach.co.uk)

p.s: Having just tested it, I see that without this compound primary  
key, rails creates duplicate rows as I suspected.

---

<div class="post-metadata">

**Author:** ![kevben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/90ced4/32.png) [@kevben](https://www.ruby-forum.com/u/kevben)\
**Post date:** [January 5, 2006, 12:48pm UTC](https://www.ruby-forum.com/t/re-many-to-many-link-table-compound-primary-key-explosion/89672/3 "2006-01-05T12:48:03Z")

</div>

I agree that putting an id column in the join table is a bad idea  
because of  
the ‘clobbering’ that will happen. The documentation even mentions  
this.  
Not to say that you can’t, but you would then have to create a different  
relationship between the models

questions (hm)…(bt) answers\_questions (bt)…(hm) answers

and answers\_questions would become a model

I might recommend the following however

class Question \< AR::Base  
has\_and\_belongs\_to\_many :answers

# only add the answer to the question only if the answer is not

already  
associated  
def add\_answer(answer)  
answers \<\< answer unless answers.include?(answer)  
end  
end

a = Answer.create(…)  
q = Question.find(1)  
q.add\_answer(a)

---

<div class="post-metadata">

**Author:** ![kevben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/90ced4/32.png) [@kevben](https://www.ruby-forum.com/u/kevben)\
**Post date:** [January 5, 2006, 1:39pm UTC](https://www.ruby-forum.com/t/re-many-to-many-link-table-compound-primary-key-explosion/89672/4 "2006-01-05T13:39:14Z")

</div>

Hi Chris,

thanks, I’ll use that method.

Do you think ActiveRecord’s behaviour is correct? Or should it check  
for duplicates itself here? Without a compound primary key this might  
have gone undetected and I think DHH’s “single layer of cleverness”[1]  
post has people believing the db shouldn’t be checking this stuff.

Should I perhaps file a bug?

John.  
[http://johnleach.co.uk](http://johnleach.co.uk)

[1] [http://www.loudthinking.com/arc/000516.html](http://www.loudthinking.com/arc/000516.html)  
I’m not suggesting that _is_ what DHH is saying though 🙂