Sample Header Ad - 728x90

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