如何在MySQL中匹配id

Question

I have two tables in my database, one with some userinfo (usersTable) and another one with submissions (submissionsTable).

usersTable has an pk usersId, so everytime someone registers they get a certain Id.

When a registered person submits something, the submission goes to submissionsTable. I put a usersId in submissionsTable that I would like to match with the usersId from usersTable. So for example everytime someone with usersTable.usersId = 15 submits something, I want submissionsTable.usersId to be automatically 15. I have no idea how, I'm stuck.

Can anyone help?

Answer 1

Use foreign key concept. Add this to your table description:

foreign key (submissionsTable.usersId) references usertable (usersTable.usersId)

You can also add on delete cascade at the end, so that when a user is deleted its submission data is also deleted.

if you are altering an existing table:

ALTER TABLE submissionsTable ADD FOREIGN KEY submissionsTable(usersId) REFERENCES usersTable(usersId) ON DELETE CASCADE

After this, your submissionsTable will be taking only the valid values from usersTable. But still, you will have to run a query to store that value in you database.