1

I have a list of organizations that contain users and locations.

Schema:

organizations (
  organization_id
)

locations (
  location_id,
  organization_id
)

users (
  user_id,
  organization_id
)

user_locations (
  user_id,
  location_id
)

I have a table to contain which locations are assigned to which users.

My issue is that my user_locations table can have a user with locations from any organizations. I want to create a constraint of some kind that will check that the location_id assigned to the user_id are within the same organization_id.

Is this type of "grandparent" constraint/key possible without having to build triggers to check?

Grant Martin
  • 125
  • 1
  • 2
  • 16
  • It may require a trigger. You can't use a `CHECK` constraint because subqueries aren't allowed. – Barmar Sep 28 '21 at 20:41
  • It feels like there should be a way to normalize the tables to prevent it, but I'm not sure how. – Barmar Sep 28 '21 at 20:42
  • This is a faq. Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. PS Add the grandparent "type" (whatever attributes you want descendants to match) to its descendants' FKs. – philipxy Sep 28 '21 at 21:05
  • Apologies if this was a FAQ, I had searched for a few hours but perhaps my google-fu is not up to par. As per your PS: I interpreted this as adding the `organization_id` column to the `user_locations` table and creating a foreign key between `organization_id` and each key in the pair, this seems to work and is exactly what I was looking for. Thanks a bunch! PS: If you want to answer this for the reputation, you have a correct solution. – Grant Martin Sep 28 '21 at 21:53
  • Duplicates of this question are somewhat hard to find since people seldom clearly phrase it. You don't either. Re answering, frequent duplicates should be closed (have answers blocked), not answered. See what you can find searching clear phrasings per my research comment & you can click that this a duplicate. PS Please clarify via edits, not comments. PS See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify one non-sole non-poster commenter `x` re a comment. Posters, sole commenters & followers of posts always get notified. – philipxy Sep 28 '21 at 23:18
  • @philipxy I've answered thousands of MySQL questions. I may have seen similar questions occasionally, but it's not frequent, and I don't have a duplicate in my collection. – Barmar Sep 29 '21 at 03:43
  • @Barmar Googling one phrasing I recall, 'site:stackoverflow.com constraint two tables away sql' [Foreign key constraints involving multiple tables 7 years ago](https://stackoverflow.com/q/26065212/3404097) [CONSTRAINT to check values from a remotely related table (via join etc.) 6 years, 10 months ago](https://stackoverflow.com/q/27107034/3404097) [How to enforce by constraint that a referenced row also has a given type 2 years, 6 months ago](https://stackoverflow.com/q/55464282/3404097) And note all the links to more in the answers. – philipxy Sep 30 '21 at 04:50
  • @Barmar Via SO/SE search `https://stackoverflow.com/search?q=user%3A3404097+predicate+fk+constrain+tag` [Group dependency SQL design 4 years, 7 months ago](https://stackoverflow.com/q/42165377/3404097) I'd guestimate in my relational model basics tags the same design question is asked maybe once a week or 2. – philipxy Sep 30 '21 at 04:51
  • @philipxy I'm not sure how useful the questions involving FK's to multiple related tables are. There's some similarity because the related table is analogous to the `organization_id`, but the solution probably wouldn't be the same. – Barmar Sep 30 '21 at 15:02

0 Answers0