1

tldr; prevent the same Asset from being attached to the same Issue more than once.

I am creating a Problem DB for my school computer assetts.

Currently I have a many to many relationship between Issue and Asset, along with pivot table of Asset_Issue. 

Issue
id - integer
title - string

Asset
id - integer
owner - string

Asset_Issue
asset_id - integer
issue_id - integer
specific_asset_issue_problem - string

I was able to attach the same Asset to the same Issue multiple times.

I would like to prevent the same Asset from being attached to the same Issue more than once, but I still want to have the pivot (Asset_Issue) as it's storing other information relating to the Asset for the Issue.

extensa5620
  • 681
  • 2
  • 8
  • 22
  • Hi, I'm not sure I understand what it is you would like to do. Is it that you want to prevent the same asset from being attached to the same issue more than once? Or should once issue only have one asset? – D Malan Jun 19 '19 at 11:03
  • Hello Delena, I want to prevent the same asset from being attached to the same issue more than once. Thanks for clarifying – extensa5620 Jun 19 '19 at 11:09
  • 1
    on a database level, you could add a unique key to the `Asset_Issue` table on the `asset_id` and `issue_id`, you'd be able to add that to the migration if you have one. – martincarlin87 Jun 19 '19 at 11:18
  • Hey martincarlin87, I thought about that. Would adding the unique key also restrict an Issue from having one or more Assets. Would you know of any other method apart from placing a unique key? – extensa5620 Jun 19 '19 at 11:22

1 Answers1

0

You could use a composite unique index. Add this to a migration:

$table->unique(['asset_id', 'issue_id']);

This would prevent identical combinations of asset_id and issue_id, e.g. this would not be allowed:

asset_id | issue_id
1        | 1
1        | 1

but this would be allowed:

asset_id | issue_id
1        | 1
1        | 2
2        | 1
2        | 2
D Malan
  • 10,272
  • 3
  • 25
  • 50