CODEWITHSUNDEEP
sql-serversql-server-2014
Kevin Maxwell
Kevin Maxwell

Reputation: 907

Reference one foreign key to multiple primary keys in SQL Server 2014

I have three tables in my database.

  1. MedicalTest [MedicalTestID PK]
  2. SportTest [SportTestID PK]
  3. GeneralNeeds [GeneralNeedsID PK]

enter image description here

Inside GeneralNeeds there is ProcessID which needs to keep MedicalTestID and SportTestID GUID.

As soon as I insert an existing GUID from either tables I get "Conflict" error message which I believe it means the given GUID doesn't exist in the other table.

For example: If I insert the GUID from MedicalTest then it will tell me that the SportTest doesn't have the given GUID.

enter image description here

Is it possible to reference multiple keys to one column?

enter image description here

Upvotes: 3

Views: 3039

Answers (1)

dmeglio
dmeglio

Reputation: 2830

The design I'd recommend here is a table/sub-table solution. What that means is, introduce a 4th table, Test. The Test table just serves as a table that links all the different test types together. If you're familiar with Object Oriented Programming, think of it as a base type. So essentially, your ProcessID points to Test which then points to one of the specific test types (SportsTest, MedicalTest, WhateverTest). If you like, you could also include a Type in the Test table to help you know which type of test it is. There is a key thing here: In the MedicalTest and SportsTest table, the primary key (say MedicalTestID) is also a foreign key that points to the TestID in Test. In this way you can use a single column in your GeneralNeeds because the reference is to a single table type, Test. Since Test and the corresponding MedicalTest or SportsTest would have the same primary key, you can easily do the joins you need to do the query.

Here is a sample diagram illustrating it:DB Diagram

Simple example: Say you have a MedicalTest with ID of 1 (let's forget GUIDs for a minute to make it easier to read) Then you'd also have a record in Test with an ID of 1. Then the ProcessID of 1 really points to the Test record which you could then join to the MedicalTest record.

If you're still confused let me know and I'll try to add a bit more detail but hopefully this gets you going in a direction that works.

Upvotes: 3

Related Questions