1

Create table scripts:

Create Table [Card]
(
    GiveDate Date not null, 
    PN nvarchar(50) not null, 
    FOREIGN KEY (PN) REFERENCES Patient (PN),
    PRIMARY KEY (GiveDate,PN)
)

Create table [Registration]
(
    EntryDate Date not null,
    ExitDate Date, 
    RoomId int not null,
    CardGiveDate date not null, 
    PN nvarchar(50) not null,
    PRIMARY KEY (PN, EntryDate, CardGiveDate),
    FOREIGN KEY (PN, CardGiveDate) REFERENCES [Card](PN, GiveDate)
)

I looked at this but it doesn't help me.

Card table has primary key

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tariq Hajeer
  • 308
  • 2
  • 14

1 Answers1

2

The PK in Card is (GiveDate, PN) , but your FK references a key (PN, GiveDate) - the order of the columns must match! So try this in your Registration table:

Create table [Registration]
(
    EntryDate Date not null,
    ExitDate Date, 
    RoomId int not null,
    CardGiveDate date not null, 
    PN nvarchar(50) not null,
    PRIMARY KEY (PN, EntryDate, CardGiveDate),
    -- make sure to specify the columns in the same order as they are defined in the referenced table!
    FOREIGN KEY (PN, CardGiveDate) REFERENCES [Card](GiveDate, PN)
)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @TariqHajeer: again , the **order of the columns** in the FK constraint **must match** the definition of the PK in the referenced table - simple as that. `FOREIGN KEY (EntryDate,CardGiveDate,PN) REFERENCES [Registration](PN, EntryDate, CardGiveDate)` – marc_s Jun 20 '21 at 12:10