Consider I have 2 classical tables book and author.

I need to insert a book where author name can sometime be found in the author table.

Then I would like to query how many book each author in the author table have.

Can we have a key relationship between these two tables? Or we should just consider them as 2 separate tables and to query the number of book per author I just need to do a join query?

I doubt that foreign key can help me here as not all authors in the book table are present in the author table.

Should I go with two separate table with no link between them? Thank you.

CodePudding user response：

Foreign key is not about querying data. It's help to store data consistent. In your case when you add foreign key on table books point to authors table you strict insertion books where author not exists on foreign table and prevent delete author when his books presents in books table.

In real case one book can have many co-authors, so you should implement many-to-many relationship, using third table

create table books (id int primary key, name varchar(64));

create table authors (id int primary key, name varchar(64));

create table books_authors (
    book_id int,
    author_id int,
    primary key (book_id, author_id),
    constraint fx_book_id foreign key (book_id) references books(id),
    constraint fx_author_id foreign key (author_id) references authors(id)
);

sql editor online

CodePudding user response：

I agree with Slava's answer as being the best approach.

Should you have a case where you wanted only 0 or 1 items related though (i.e. if you required that every book have only one author) you can do that with a foreign key in the book table pointing at the author table. To allow this to be optional, just allow that value to be null (i.e. it is by default if you don't include not null in that field's statement).

create table author (
  id bigint not null identity(1,1) primary key clustered
  , name nvarchar(256) not null
)

create table book (
  id bigint not null identity(1,1) primary key clustered
  , title nvarchar(256) not null
  , authorId bigint foreign key references author(id)
)

To query this you'd then do

select * 
from book b
left outer join author a
on a.id = b.authorId

Or for all books by a certain author:

select * 
from book b
inner join author a
on a.id = b.authorId
where a.name = 'Skeet, Jon'