CODEWITHSUNDEEP
sql-serverdatabasedatabase-designentity-framework-coredatabase-schema
variable
variable

Reputation: 9664

How to enforce a 1:M relation in a M:M?

I am designing a database schema.

There are presently 2 tables:

  1. Task (TaskID, TaskName)
  2. Description (DescriptionID, Description)

A task can have multiple descriptions, but the same description shouldn't be shared by multiple tasks. A description can have 0 or 1 task (User should be able to add description without task and be able to link a task later).

Option 1:

Add TaskID as nullable foreign key in the Description table.

Option 2

Instead, I can create another table called TaskDescription(RowID, TaskID, DescriptionID). But it is now a M:M. A task can have multiple descriptions, and a description can be shared by multiple tasks. In this case, how to prevent more than 1 task from sharing the same description?

Upvotes: 0

Views: 84

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Option 2

Instead, I can create another table called TaskDescription(RowID, TaskID, DescriptionID). But it is now a M:M. A task can have multiple descriptions, and a description can be shared by multiple tasks. In this case, how to prevent more than 1 task from sharing the same description?

Only two columns with a composite primary key (on both columns, obviously) would suffice for another table, along with unique index on descriptionID which will enforce

but the same description shouldn't be shared by multiple tasks

Something like this (Oracle syntax; disregard that. I hope it is easier to see what I mean by looking at some code, rather than reading my non-native English speaking description):

create table taskDescription
  (taskID         number  constraint fk_td_task references task (task_id),
   descriptionID  number  constriant fk_td_desc references description (descriptionID),
  --
  constraint pk_td primary key (taskID, descriptionID)
  );

create unique index ui1_td on taskDescription (descriptionID);

Doing so, database will handle everything and raise an error if you try to violate any of enforced constraints.

Upvotes: 4

Related Questions