SQL/ACCESS one-sided one-to-one relationship data integrity

0
votes

I have a DB that contains two tables for now:

  • "Users": includes data loaded from a CSV.
  • "Accounts": Account opened for a certain user.

Both tables have numeric ID field ("UserID", "AccountID"), as the "Accounts" table also has a field called "UserID" for linking these two tables together.

I wish to connect these two tables to make sure that new records in "Accounts" can be added only if they have a matching inserted UserID that is found in the "Users" table. It sounded to me at first like a connection of one-to-one. However, while there can't be an account with a user that is not in the "Users" table, most of the records in "Users" still don't have an account in the "Accounts" table.

Is there any way to enforce data integrity about this relationship in ACCESS/SQL?

1
sqlms-accessone-to-onedata-integrity
Have you googled MS Access foreign key? - jarlh
Yes, can set relationship to enforce relational integrity so records in Accounts must have parent record in Users. However, to prevent multiple Accounts for a user, have to set the UserID 'foreign key' in Accounts as a primary key. Then don't have AccountID in Users. Why is this not one table? - June7

1 Answers

0
votes

Yes you can by using foreignKeys. After you create the table Users with UserID as PrimaryKey, when you create the table Accounts with AccountsID as primaryKey you have to add UserID as a foreignKey and with a reference to UserId from Users table

  FOREIGN KEY (userID) REFERENCES Users(UserID)