# Two foreign keys on the same column?

**URL:** <https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967>\
**Category:** Rails\
**Created:** [March 2, 2006, 10:10pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967 "2006-03-02T22:10:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![justinskolnick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b5a626/32.png) [@justinskolnick](https://www.ruby-forum.com/u/justinskolnick)\
**Post date:** [March 2, 2006, 10:10pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967/1 "2006-03-02T22:10:23Z")

</div>

Let’s say I have three hypothetical MySQL tables:

â?¢ people, with columns id, gender, and source\_id  
belongs\_to :boys and :girls

â?¢ boys, with columns id and name  
has\_many :people

â?¢ girls, with columns id and name  
has\_many :people

The gender column in people specifies which of the two source tables the  
source\_id refers to. For example, if we have values:

1, boy, 1

in people, the application knows to pull id #1 from the boys table,  
which is Roderick, and not Wilhelmina, id #1 in the girls table.

My question is whether I can perform this switch without Rails calling  
me a dumbass. Would this require two foreign keys on the same table  
column? Is this possible? Suggestions?

---

<div class="post-metadata">

**Author:** ![justinskolnick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b5a626/32.png) [@justinskolnick](https://www.ruby-forum.com/u/justinskolnick)\
**Post date:** [March 3, 2006, 8:20pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967/2 "2006-03-03T20:20:49Z")

</div>

Justin S. wrote:

> The gender column in people specifies which of the two source tables  
> the source\_id refers to. For example, if we have values:
> 
> 1, boy, 1
> 
> in people, the application knows to pull id #1 from the boys table,  
> which is Roderick, and not Wilhelmina, id #1 in the girls table.

First suggestion: Re-architect your data to actually follow the  
relational model. Your schema above has numerous problems; trivial  
examples include names like Jessie duplicated in both the “boys” and  
the “girls” table; the inability to deal with people with ‘wrong’-sexed  
names; different regions/countries assigning different sexes to the  
same name; etc.

Now, I suppose you could make rails deal with this by something like  
this in your Person model:

def name  
(gender == ‘m’) ? Boy.find(source\_id) : Girl.find(source\_id)  
end

---

<div class="post-metadata">

**Author:** ![justinskolnick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b5a626/32.png) [@justinskolnick](https://www.ruby-forum.com/u/justinskolnick)\
**Post date:** [March 3, 2006, 9:03pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967/3 "2006-03-03T21:03:05Z")

</div>

With a little tweaking you could make this work as a polymorphic  
association.

\_Kevin

---

<div class="post-metadata">

**Author:** ![justinskolnick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b5a626/32.png) [@justinskolnick](https://www.ruby-forum.com/u/justinskolnick)\
**Post date:** [March 3, 2006, 8:40pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967/4 "2006-03-03T20:40:00Z")

</div>

But my index on ‘people’ would be ‘source\_id’ – not ‘name’ – referring  
to the primary key (‘id’) of the two other tables. I’d use the ‘gender’  
column to determine which table (‘boys’ or ‘girls’) the ‘source\_id’  
should reference.

So it shouldn’t matter whether the name is gender-neutral or a  
nonsensical string of characters.

My question: Is it possible to place two foreign keys on the same column  
(source\_id)? Or should I scrap the whole switching mechanism?

The code will help, thank you.

---

<div class="post-metadata">

**Author:** ![justinskolnick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b5a626/32.png) [@justinskolnick](https://www.ruby-forum.com/u/justinskolnick)\
**Post date:** [March 3, 2006, 10:52pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967/5 "2006-03-03T22:52:09Z")

</div>

Justin S. wrote:

> But my index on ‘people’ would be ‘source\_id’ – not ‘name’ –  
> referring to the primary key (‘id’) of the two other tables. I’d use  
> the ‘gender’ column to determine which table (‘boys’ or ‘girls’) the  
> ‘source\_id’ should reference.

If I’ve understood correctly, you are trying to use tables along the  
lines of (in PostgreSQL syntax, ‘serial’ is like auto\_increment in  
MySQL)

CREATE TABLE boys(  
id SERIAL PRIMARY KEY,  
name TEXT UNIQUE NOT NULL  
);

CREATE TABLE girls(  
id SERIAL PRIMARY KEY,  
name TEXT UNIQUE NOT NULL  
);

CREATE TABLE people(  
id SERIAL PRIMARY KEY,  
gender CHAR(1) NOT NULL,  
source\_id INTEGER NOT NULL,  
CONSTRAINT ‘sane\_gender’ CHECK (gender = ‘m’ or gender = ‘f’)  
);

And some data:  
SELECT \* FROM boys;  
id | name  
----±-----  
1 | Tom  
2 | Bob  
3 | Jessie

SELECT \* FROM boys;  
id | name  
----±-----  
1 | Sue  
2 | Jane  
3 | Jessie

SELECT \* from people  
id | gender | source\_id  
----±-------±----------  
1 | ‘m’ | 1  
2 | ‘f’ | 1

Ok. Expanding this, there is one guy named Tom, and one gal named Sue.  
Works so far — well, except for repeating yourself with “Jessie” in  
both the “boys” and “girls” tables. Now, a guy named Jane — yes, a  
guy named Jane — comes along, so you add:

INSERT INTO PEOPLE(gender, source\_id) VALUES(‘m’, ‘2’);

But wait! That doesn’t work; he’s now Bob. Instead, you have to first  
add ‘Jane’ to the boys table. So why not use:

CREATE TABLE names (  
id SERIAL PRIMARY KEY,  
name TEXT NOT NULL UNIQUE  
);

CREATE TABLE people (  
id SERIAL PRIMARY KEY,  
gender CHAR(1) NOT NULL,  
name\_id INTEGER NOT NULL REFERENCES names,  
CONSTRAINT ‘sane\_gender’ CHECK(gender = ‘m’ or gender = ‘f’)  
);

Now, maybe you want to be able to find names for only one sex; if so:

CREATE TABLE names (  
id SERIAL PRIMARY KEY,  
name TEXT NOT NULL UNIQUE,  
is\_boy\_name BOOLEAN NOT NULL,  
is\_girl\_name BOOLEAN NOT NULL  
);

Now, you’ve got the ability to find which names are generally boys,  
which are generally girls, and even which (like Jessie) can be either.  
Not only that, you can handle exceptions — a boy with a girl’s name,  
or vice versa.

And, you can query for other information. For example, to find all the  
guys who don’t have a guy’s name:

SELECT p.id, n.name  
FROM people p, names n  
WHERE  
p.name\_id = n.id AND (p.gender = ‘m’ AND NOT n.is\_boy\_name)

---

<div class="post-metadata">

**Author:** ![justinskolnick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b5a626/32.png) [@justinskolnick](https://www.ruby-forum.com/u/justinskolnick)\
**Post date:** [March 3, 2006, 11:26pm UTC](https://www.ruby-forum.com/t/two-foreign-keys-on-the-same-column/75967/6 "2006-03-03T23:26:24Z")

</div>

Honestly, my problem has nothing to do with boys and girls.

The two tables (in my example, boys and girls) must exist separately.  
You assume the ‘names’ are UNIQUE, but that’s not necessary to my  
purpose. As long as the primary key (id) is unique, every boy’s name can  
be Dick and every girl’s name Jane. Or everyone can be Dick. Doesn’t  
matter. Repetition is fine.

My two, real-life tables (unlike the boys and girls examples) have  
almost nothing between them in common. What I need to do is, based on a  
single record, tell Rails which of these two tables to pull a subsequent  
record from.

- gender = ‘m’ and source\_id = 1? OK, pull record 1 from the boys table.
- gender = ‘m’ and source\_id = 3? OK, pull record 3 from the boys table.
- gender = ‘f’ and source\_id = 2? OK, pull record 2 from the girls  
table.
- gender = ‘f’ and source\_id = 3? OK, pull record 3 from the girls  
table.

And so on. That’s it.

I appreciate your help but it doesn’t much apply to what I’m doing.

A polymorphic association sounds promising. Can you point me to  
documentation?