Ensuring relationship between child tables exists prior to SQL insert I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.
I could write a sql statement like this: insert into test (name, user_id, assignment_id) values (&quot;final exam&quot;, 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.
The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I'm wondering if I can accomplish both in one query though. I'm not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.
Recommended scheme:
Existing tables (PK == Primary key):
user (user_id PK, etc.)
assignment (assignment_id PK, etc.) 
test (test_id PK, etc.)

Relations tables (PK == Primary key, FK == Foreign key):
user_assignment (user_assignment_id PK, user_id FK, assignment_id FK, etc.)

This table creates the relation &quot;user to assignment&quot; which would check to see if the user and assignment exist.
user_test (user_test_id PK, user_assignment_id FK, test_id FK, etc.)

This table creates the relation &quot;user with existing assignment to test&quot;.