Table PERSON vs EPISODE) foreign key columns to match exactly

[pmoarshaduk]

SQL Server requires foreign key columns to match exactly.
integer ≠ bigint, so SQL Server refuses to create the FK

How to Fix It
You have two options — choose the one that matches your data model.

Option 1 — Make PERSON.person_id a BIGINT
This is the most common fix if your IDs can grow large.

Option 2,. Not fit for us, as int is not appropriate for person key.

Important Note (OMOP CDM)
Since you’re clearly building OMOP CDM:

PERSON.person_id must be BIGINT

EPISODE.person_id must also be BIGINT

So you’re absolutely doing the right thing.

===============================================
ALTER TABLE PERSON
ALTER COLUMN person_id BIGINT NOT NULL;

ALTER TABLE EPISODE
ADD CONSTRAINT fpk_EPISODE_person_id
FOREIGN KEY (person_id) REFERENCES PERSON(person_id);

ALTER TABLE PERSON
DROP CONSTRAINT xpk_PERSON;

ALTER TABLE PERSON
ALTER COLUMN person_id BIGINT NOT NULL;

ALTER TABLE PERSON
ADD CONSTRAINT xpk_PERSON PRIMARY KEY (person_id);

ALTER TABLE EPISODE
DROP CONSTRAINT fpk_EPISODE_person_id;

ALTER TABLE EPISODE
ADD CONSTRAINT fpk_EPISODE_person_id
FOREIGN KEY (person_id) REFERENCES PERSON(person_id);