CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005database-design
Asdfg
Asdfg

Reputation: 12213

Composite foreign key in SQL Server

I have a table named Books which contains 3 columns.

TableName: Books
Columns: BookId (PK), BookName, Book_Publisher_XRef_Id (FK), IsInternal

I have two tables that contains publisher information. Both these tables have different set of columns.

TableName: InternalPublishers
Columns: PublisherId (PK), PublisherName, ....

TableName: ExternalPublishers
Columns: PublisherId (PK), PublisherName, ....

I have a link table that contains information about which book belongs to which publisher. One book can have multple publishers.

TableName: Books_Publishers_XRef
Columns: Book_Publisher_XRef_Id (PK), PublisherId

If I want to create a Foreign Key constraint on PublisherId, I need to create sort of Composite Foreign Key constraint which I am not sure can be created.

So in this scenario, what is the best way to achieve FK on PublisherId in Books_Publishers_XRef table?

  1. Break Books_Publishers_XRef table in 2 tables i.e. one for Internal Publishers and another one for External Publishers and have 2 columns in Books table for Books_Internal_Publishers_XRef and Books_External_Publishesr_XRef tables?

  2. Don't create FK on Publisher_Id column and leave the design as it is?

  3. Create composite FK by adding Publisher_Type_Id column in Books table and Books_Publishers_XRef table where if Publisher_Type_Id = 1, it belongs to Internal_Publishers table and Publisher_Type_Id = 2, it belongs to External_Publishers table ? (Not sure if this is possible)

  4. Some other schema design?

Please advise.

Upvotes: 1

Views: 545

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

  • Keep all common columns in the Publisher table.
  • Subtype tables have only columns specific to each one.

enter image description here

Upvotes: 1

user596075
user596075

Reputation:

Don't divide your data amongst two tables: InternalPublishers, ExternalPublishers. Create one table and have a bit field to determiner whether they are internal or external. Something like this:

create table Publisher
(
    PublisherId int not null primary key clustered,
    PublisherName varchar(100) not null,
    IsInternal bit not null
)
go

That way you can easily create your foreign key reference. After all, you seem to have this same design for Books, keep that going to publishers.

Upvotes: 1

Related Questions