1

I have an issue where I need to create a relationship between two tables, but the uniqueness is only between two fields in both of the tables. Neither one of the values is unique on their own. The database being used is currently in SQL Server 2008 R2. Here's the actual scenario:

Table B contains a JobNum column, which is already a foreign key to Table A. Table B also contains a column named Shipment. This Shipment column is not unique on its own, but when paired with a JobNum the composite is unique. (JobNum AND SHIPMENT = UNIQUE) I know that I can create a unique index of the composite. However, since I inherited this project there is already a primary key, which is simply an auto-incrementing identity. Table C contains both JobNum and Shipment columns, but there is no actual relationship between the two tables.

How can Table B be linked to Table C using this composite unique value? Does this need to be done with a join table?

JNYRanger
  • 6,829
  • 12
  • 53
  • 81
  • What does "there is no actual relationship between the two tables" mean, ie concretely in terms of values in the tables? Ie when a tuple is C, what does it tell you about the application situation? And B? and A? Only that can tell you how B and C or A and B (or B and A or A, B and C) are "linked". – philipxy Jul 03 '14 at 14:33
  • @philipxy I was simply stating that there is not a relationship set in terms of foreign key constraints within the database. There is a relationship in terms of the actual values within the data though. – JNYRanger Jul 03 '14 at 15:48
  • Ok: No declared constraint. Do you understand though, that one needs to know what a tuple says about the application situation in order to determine the constraints? Are you expecting us to assume that you are trying to declare a fk from B to C on those two fields? – philipxy Jul 03 '14 at 20:13

1 Answers1

2

A foreign key may consist of more than one column, as long as both tables have the same two fields of same types; therefore, you can create a unique key for JobNum and Shipment in Table B, and make it be a foreign Key of Table C:

CONSTRAINT fk_mrt_ot FOREIGN KEY (refcol1, refcol2)
                    REFERENCES OtherTable(col1, col2)

by the way, as seen here

Community
  • 1
  • 1
DMorillo
  • 86
  • 4
  • Keep in mind that this is in MS SQL Server, not MySQL – JNYRanger Jul 03 '14 at 13:30
  • No problem! I saw lots of info on MySQL for this situation, but very little for SQL Server. I know they are similar, but I want to make sure I understand it correctly for the database I inherited. – JNYRanger Jul 03 '14 at 13:36
  • Turns out that this worked without an issue (the actual SQL was different, but concept was dead on). I just had to made a unique key index first on the composite of the columns. Then it accepted the relationship. – JNYRanger Jul 08 '14 at 16:34