CODEWITHSUNDEEP
database-designforeign-keysprimary-keycomposite-primary-key
Debanik Dawn
Debanik Dawn

Reputation: 799

How to make Primary Key for Weak Entity

I have a Course entity for my college database. The schema is as follows:

Course(name, department, section, semester, teacher_id, college_id)

It is a weak entity with College as its owner entity. (Primary key for college is college_id which is stored as the foreign key)

My question is... How do I form the primary key for Course?

I mean, technically I could make a composite primary key out of all the attributes of Course but if I have to reference this composite primary key as a foreign key in some other table, that would be a huge headache. Or would it? Is there any alternate ways I could acheive the same?

Any help is appreciated. Thanks in advance.

Upvotes: 0

Views: 4693

Answers (1)

reaanb
reaanb

Reputation: 10064

Weak entities necessarily have composite primary keys, and referencing those weak entities in relationships will involve composite foreign keys. If you think that's a headache, you can introduce a surrogate key which will turn it into a regular entity.

A surrogate key is simply a new identifying attribute. For example you could add a course_id attribute which can be implemented as an autoincrement column in the Course table.

When introducing surrogate keys, take care to declare unique constraints for any other candidate keys (such as the composite key you would've used before introducing the surrogate key).

Upvotes: 3

Related Questions