Existing tables adding foreign key constraints

[Matthew Lenz]

imgurDOTcom/a/NVlFVl4

I've got the above set of tables ER diagram above.

members have many users
members have many approval_groups
approval_groups have many approval_group_users

approval_group_users.member_id must be of the same member as it's approval_group.

approval_group_users.user_id must be of the same member.

I can add foreign keys to satisfy all these constraints but it involves creating composite unique keys on user.member_id and user.id as well as approval_group.member_id and approval_group.id.

It just feels wrong creating unique keys that use the primary key though. I'm aware that this entire model might be improved by not putting member_id in the tables and using link tables but as soon as I say something like approval_group.name must be unique by member that all breaks down and I'm back to where I started.

Am I crazy in thinking that these composite keys using the primary key are the only way to implement this constraint? I could just say forget it not bother enforcing integrity with users and approval_groups with their members.

One other note is that I added approval_group_user.member_id to satisfy the constraints I need. These are a simplified examples of existing tables in a real application that has no FKs defined currently. Adding a new field to these tables is an easy change. Adding new tables would be prohibitive.

I'd love some feedback from the pros. Thanks!