sql - one column foreign key to two column primary key [duplicate]

This question already has answers here: Closed 11 years a开发者_如何学Gogo.

Possible Duplicate:

Can a foreign key reference a non-unique index?

I was just porting one application from MySQL to PostgreSQL and MS SQL Server and I found strange (at least to my knowledge) definition.

This is simplified example.

How come is this possible in MySQL and how should it behave?

create table t1 (a int, b int, primary key (a, b))

create table t2 (c int, a int references t1 (a))

t1.a is not unique, not even t1.b. Together they create unique record and that makes the primary key. t2.a is a foreign key reference to t1.a, but t1.a is just part of the primary key in t1.

What do you think about this?

Obviously the database design is wrong? If so, how come this is allowed in MySQL?

Thanks!

---

 What do you think about this?

I think it depends on your data. I have a SQL server database I use all the time. For most tables, the primary key is a combination of entity ID and program number. Entities have multiple programs. Yet I also have a table that just has information about each entity and only links to other tables through the entity ID.

It's a valid relationship for some data.