How can I delimit the possible values of a foreign key?

Question

I have three tables in a MySQL DB. This is the main table with organisation related stuff. Every Organisation has an unique identifier which is also the foreign key in some tables.

org
+------------+-------------+ 
| org_id     | name        |   
+------------+-------------+ 
| 1          | a           | 
| 2          | b           |  
| 3          | c           |  
+------------+-------------+

This is the groups table. Organisations can have many groups.

groups
FOREIGN KEY (ORG_ID) REFERENCES ORG (ID);
+------------+-------------+----------+ 
| ID         | org_id      |   name   |
+------------+-------------+ ---------+
| 1          | 1           |  Group1  |
| 2          | 2           |  Group2  |
| 3          | 2           |  Group3  |
+------------+-------------+----------+

And this is the feed table in which I would like to perform an update. A feed can have only one associated group.

feed
FOREIGN KEY (GROUP_ID) REFERENCES GROUPS (ID);
+------------+-------------+--------------+ 
| ID         | org_id      |   group_id   |
+------------+-------------+ -------------+
| 1          | 1           |      1       |
| 2          | 2           |      2       |
| 3          | 1           |     NULL     |
| 4          | 2           |      3       |
+------------+-------------+--------------+

So now there is one problem, that i can't solve. When I INSERT or UPDATE a row, I set the groups_id, but this can also be a groups_id which not belongs to the organisation. This happens, because all ID's in GROUPS are valid FK values. That's a thing I want to avoid. It should only be possible to insert or update a row with a groups_id which has also the same org_id as in feeds.org_id.

As you can see, the data is now fine. But when I try to make this INSERT INTO feed VALUES (4, 2, 1) it were nice to see an error. Yeah, right, I'm missing an lovely error....

It is difficult for me to make an connection between them. There seems one information or method that I'm missing. I've been looking for a lot, but I don't know the words to describe my problem.

So I ask you, could you give me a tip?

EDIT:

All feeds and all groups are related to an organisation, which has an identifier. An organisation can create feeds/messages. When this feeds are not associated with a group, this feed ist public. For special feeds they can create a group. This group is related to this special organisation.

This works and everything is good:

UPDATE feed 
SET title = "Title", message = "Message", groups_id = "1" 
WHERE id = "1" AND org_id = "1"

But this works also:

UPDATE feed 
SET title = "Title", message = "Message", groups_id = "2" 
WHERE id = "1" AND org_id = "1"

The problem is, that it is possible to associate a group to a feed (which is associated to org 1), while the group is not associated with the org (group 2 is associated with org 2).

So my thought was, is there a way to solve this through FOREIGN KEY or similar (checks, joins, subqueries). Or should I think about my db design?

Answer 1

I think a composed foreign key solves your problem:

create table agroup (
  id int primary key,
  orgid int,
  UNIQUE (id,orgid)
);

create table feed (
  id int primary key,
  groupid int,
  orgid int,
  FOREIGN KEY (groupid, orgid) REFERENCES agroup(id, orgid)
);

insert into agroup values (10, 1), (20, 1), (30, 2), (40, NULL);
insert into feed values (100,10,1), (101, 20, 1);
insert into feed values (102, 40, NULL); # works
insert into feed values (103, NULL, 1); # works as well
# insert into feed values (110,10,2); # yields error "Cannot add or update a child row: a foreign key constraint fails"

Note the UNIQUE(id,orgid), which seems to be necessary. Though I do not understand why agroup(id primary key) is not sufficient to make also agroup(id,orgid) unique, I got a compiler error without this explicit unique(id,orgid)-constraint. Documentation says that the referenced attributes must be indexed. Anyway, your problem should be solved.

EDIT: Extended example, which now demonstrates also the case of NULL-values in referencing attributes.

At least in MySQL, a composite foreign key constraint permits NULL values in the referencing (child) rows, regardless of whether the parent table contain rows with corresponding NULL-values or not. If one inserts a row with NULL-values for foreign-key attributes, the foreign key constraint is simply ignored. Confer mysql foreign key semantics, which says: "... MySQL essentially implements the semantics defined by MATCH SIMPLE, which permit a foreign key to be all or partially NULL. In that case, the (child table) row containing such a foreign key is permitted to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers."