Does adding a surrogate key get rid of an identifying relationship between two entities?
3
votes
1
answer
449
views
In the following example, we have a
Comments
table with a composite primary key (PK)
CREATE TABLE Comments (
video_id INT NOT NULL,
user_id INT NOT NULL,
comment_dt DATETIME NOT NULL,
PRIMARY KEY (video_id, user_id, comment_dt),
FOREIGN KEY (video_id) REFERENCES Videos(video_id),
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
There appears to be an identifying relationship, as the primary key of Comments
includes its foreign keys.
However, watch what happens when you give this Comments
table a surrogate primary key of its own:
CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
video_id INT NOT NULL,
user_id INT NOT NULL,
comment_dt DATETIME NOT NULL,
FOREIGN KEY (video_id) REFERENCES Videos(video_id),
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
Does that get rid of the identifying relationship? Logically, it still seems to be there, so does this really come down to the *physical* model and whether or not you want to include a composite key? Are identifying relationships even necessary?
Asked by brennvo
(91 rep)
Feb 1, 2019, 08:06 PM
Last activity: Feb 2, 2019, 08:22 PM
Last activity: Feb 2, 2019, 08:22 PM