Managing Foreign Keys

Question

So I have a database with a few tables.

The first table contains the user ID, first name and last name.

The second table contains the user ID, interest ID, and interest rating.

There is another table that has all of the interest ID's.

For every interest ID (even when new ones are added), I need to make sure that each user has an entry for that interest ID (even if its blank, or has defaults).

Will foreign keys help with this scenario? or will I need to use PHP to update each and every record when I add a new key?

Answer 1

Foreign keys are a kind of constraint, so they can only fail when you attempt to add records.

You can accomplish what you are describing with a trigger. I don't know the MySql syntax, but in SQL Server it would look something like this:

CREATE TRIGGER TR_ensure_user_interest ON interest FOR INSERT, UPDATE AS
BEGIN
    INSERT user_interest (user_id, interest_id)
    SELECT user_id, interest_id
      FROM inserted
          ,user
    EXCEPT (SELECT user_id, interest_id)
END

Note that this is a rather inefficient approach, but it should cover many of the cases you're concerned about.

UPDATE: I agree with the others who have observed the design "smell" here. If you can accomplish the required result using JOIN queries, that would be a much more efficient solution. However, I was trying to answer the question actually asked. (Plus, I have been in this situation, where physical records are helpful to other database users who are not adept at compound queries.)

Answer 2

It sounds like you are forcing your physical design to mirror your logical design too tightly.

Maybe it would be a good idea to rethink exactly why you need to insert a row for every user in the physical table. Couldn't you just write your queries to assume the default value for an interestID if there isn't an associated interestID for a given user?

Answer 3

For every interest ID (even when new ones are added), I need to make sure that each user has an entry for that interest ID (even if its blank, or has defaults).

It sounds like you need an OUTER JOIN (either LEFT or RIGHT) in one of your queries instead.

For example, if you wanted to get the level of interest a particular person has for each interest:

Assuming your tables look like this:
users:
user_id PK
user

user_interests:
user_id PK FK
interest_id PK FK
interest_level

interests:
interest_id PK
interest

SELECT i.interest, ui.interest_level
FROM interests i
INNER JOIN user_interests ui USING (interest_id)
LEFT JOIN users u USING (user_id)
WHERE user_id = ?

? is a placeholder.

Note that ui.interest_level will be null for interests with no data.

Answer 4

"Will foreign keys help with this scenario?"

No.

Your constraint is a sort of "completeness" constraint. It implies that for each new Interest added, there must be as many rows added to the USER_INTEREST table as there are users.

No SQL system is able to enforce that for you. It's up to you to enforce it through code.