0

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!

Community
  • 1
  • 1
fritz
  • 1
  • 1
    See http://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index – Alex Howansky Jun 28 '11 at 17:45
  • Also voting to close. No offense intended, but this is already answered well in the question referenced by @Alex Howansky. – Bill Karwin Jun 28 '11 at 17:54
  • Is "t1.a" a foreign key to another table? If so (assume it is "t3") then change t2's FK constraint to point to that table. – Keith Jun 28 '11 at 17:57
  • Keith, t1.a is not foreign key to another table. It's just a column in t1 table. – fritz Jun 28 '11 at 18:07

1 Answers1

0
 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.

mikeY
  • 519
  • 4
  • 14
  • I don't think this is valid relationship. Also, such case cannot exist in MS SQL Server database - if you meant that. Doesn't work on PostgreSQL/MS SQL Server. And I really don't understand what it would mean, how can it find matching rows in case of defined CASCADE? – fritz Jun 28 '11 at 19:39
  • I have an MS SQL Server database, so it can exist. – mikeY Jun 28 '11 at 20:50