SQL ERD- primary key appears twice

Question

How can we deal with a scenario that the same primary key appears more than once?

Scenario Example:

Let's suppose I have SQL-SERVER Database of Fire station.

We would like to create a table that connects between a call request to a fire fighter

Table:

• Call Requests
• Firefighters

The table looks like:

[Call_Request_Code[INT, PRIMARY KEY], Firefighter[ID, FOREIGN KEY] ]

For example:

Request_Code-222 , firefighters ID's- 987 and 777.*

The problem is that it will have 222 twice Since the same primary key appear twice an exception will be thrown.

Answer 1

That's because Call_Request_Code is Primary Key. If i understood your request you should remove the primary key from Call_Request_Code and applay primary key constraint on both the fields.