How to deal with multi-valued foreign key in SQL Server

Question

I have a table Authors in SQL Server with Author_ID as primary key.

Authors table structure

Author_ID | Author_Name  
----------------------------
677       | Nuno Vasconcelos
1359      | Peng Shi
6242      | Z. Q. Shi  
...       | ...  
...       | ...

I have another table CoAuthors with CoAuthor_ID as primary key and with Author_ID as foreign key.

CoAuthors table structure

CoAuthor_ID | CoAuthor_Name | Author_ID  
---------------------------------------
47          | Jim Bezdek    | NULL
111         | Vishal Gupta  | NULL
318         | Muaz A. Niazi | NULL
...         | ...           | ...  
...         | ...           | ...

I have a another Author-CoAuthor mapping table Yearly_Author_CoAuthors as:

Author_ID | CoAuthor_ID | Year
------------------------------
677       | 901706      | 2005
677       | 838459      | 2007
677       | 901706      | 2007  
...       | ...         | ...  
...       | ...         | ...

Now I have to insert foreign keys in CoAuthors table getting from Authors table. The issue is I may have multiple values of Author_ID for CoAuthor_ID as for instance executing this query:

SELECT   
    Author_ID, CoAuthor_ID, Year  
FROM     
    Yearly_Author_CoAuthors  
WHERE
    CoAuthor = 901706  
ORDER BY 
    Author_ID, Year, CoAuthor_ID

And I got this output:

Author_ID | CoAuthor_ID | Year  
------------------------------
677       | 901706      | 2005
677       | 901706      | 2007
677       | 901706      | 2009
1683703   | 901706      | 2012

which is showing that CoAuthor_ID = 901706 is having two DISTINCT Author_ID, so here:

How can I insert Author_ID as foreign key constraint in CoAuthors table?

Have you Googled adding a Foreign Key? What specifically is the problem you are running into? — dfundako, May 11 '16 at 14:42
If I understand correctly, you have added the bridge table that maps Author to CoAuthor to store what dates two people worked together on a book or something? If so then do you need the one-to-many relationship between CoAuthor and Author? — MLT, May 11 '16 at 14:42
If you need it fine, but adding that won't be affecting the select query you say you are getting duplicates from. The query is only looking at the Yearly_Author_CoAuthors table. Is there an error message or something? If not then see my answer and comment below. There is nothing obviously wrong with the output from the query, that is just what data is in the Yearly_Author_CoAuthors table. If it isn't correct then you need to constrain what can be entered — MLT, May 11 '16 at 15:10
@MLT I am implementing an Entity Framework structure, for which I have to have Author_ID as foreign key in each of CoAuthors, Venue and Papers table — maliks, May 12 '16 at 11:23
@MLT The issue is I'm unable to relate Authors table with other tables i.e. CoAuthors (mentioned here), Venues and Papers (not mentioned here) — maliks, May 12 '16 at 11:32
I've given a couple of example queries showing how your Authors and CoAuthors tables are already relatable in my answer below. You can join tables in queries regardless of whether a foreign key exists but you should add them for consistency checking. The foreign key goes on the id column to the table containing the data you want to reference (i.e. the 'many' side in a one-to-many relationship), your Yearly_Author_CoAuthors table is a bridge table giving a many-to-many relationship because it has a one-to-many relationship with both tables. You should mark as answered and start another question — MLT, May 12 '16 at 13:39

score 0 · Answer 1 · answered May 11 '16 at 14:44

0

The potential many-to-many relationship between authors and co-authors does not prevent the foreign key constraint so long as the Author_ID is unique within the authors table. Simply do this:

ALTER TABLE CoAuthors
ADD CONSTRAINT FK_Authors_Author_ID FOREIGN KEY (CoAuthor_ID) 
    REFERENCES Authors (Author_ID)

answered May 11 '16 at 14:44

quest4truth

1,111
9
14

Of course if the Author_ID is not unique within the Authors table, this will not work. A foreign key requires that the reference is unique. This enables you to do things such as ON DELETE CASCADE, etc. – quest4truth May 11 '16 at 14:45
Author_ID is unique in Authors table and CoAuthor_ID is unique in CoAuthors table – maliks May 11 '16 at 15:03
Then I believe this code will work and add the foreign key constraint you need. – quest4truth May 11 '16 at 15:13

MLT · Accepted Answer · 2016-05-11T15:53:13.893

Looking at your example Yearly_Author_CoAuthors select query you must have entered the duplicate values to be returning them.

Either make Author_ID & CoAuthor_ID the compound Primary Key (without Year) or add a unique index across those two columns.

EDIT: If you don't have foreign keys on Yearly_Author_CoAuthors.Author_ID and Yearly_Author_CoAuthors.CoAuthor_ID, then I would advise firstly that you tweak the script in user3481891's answer to add them to that table. Then I don't see why you wouldn't be able to query what you already have. Here are some examples (untested I'm afraid, let me know if they don't work):

-- to get list of who a CoAuthor has worked with and when
select  a.Author_Name,
        ca.CoAuthor_Name,
        [Year]
from    Authors a inner join 
        Yearly_Author_CoAuthors yac on a.Author_ID = yac.Author_ID inner join 
        CoAuthors ca on yac.CoAuthor_ID = ca.CoAuthor_ID
where   yac.CoAuthor = 901706

-- to get a list of first occasions when a CoAuthor has worked with each Author
select  a.Author_Name,
        ca.CoAuthor_Name,
        min([Year]) as FirstCollaborationDate
from    Authors a inner join 
        Yearly_Author_CoAuthors yac on a.Author_ID = yac.Author_ID inner join 
        CoAuthors ca on yac.CoAuthor_ID = ca.CoAuthor_ID
where   yac.CoAuthor = 901706
group by a.Author_Name,
        ca.CoAuthor_Name
order by min([Year])

of course that assumes that you have the Yearly_Author_CoAuthors table to show a link between authors and the year in which it first occurred. If you expect a list of years for the author partnerships then there is nothing wrong with the output from your example query because they are all different years — MLT, May 11 '16 at 14:57
@MLT--can you add an example how to make Author_ID and CoAuthor_ID the compound primary key so do we use Yearly_Author_CoAuthors table then ? — maliks, May 11 '16 at 15:05
Sure, but first you should explain what you want to get back from your Yearly_Author_CoAuthors table query, you might just need to change the query — MLT, May 11 '16 at 15:13
Just a mo, am I missing something here though? You do have foreign keys on the bridge table to Author and CoAuthor don't you? That is where the relationships should be. Adding the extra foreign key on CoAuthors doesn't make sense to me, I don't see what you gain from adding it — MLT, May 11 '16 at 15:18

How to deal with multi-valued foreign key in SQL Server

2 Answers2