CODEWITHSUNDEEP
javaandroidsqlsqlite
User3
User3

Reputation: 2535

Special record in Database table, if changed, it should reflect changes in all other records associated

I have some four database tables One table has two fields of interest which are set by the user at first download - first run.

The user sets his name and date of birth - after processing I assign this name and DOB to various records in other tables(In other tables there are only two possibilities either the record is associated with the owner who sets the name and DOB or user can associate a record with his friend using his friends name and DOB). Now I want to give the use a functionality to edit this record which is his name and DOB. However editing this one record should also trigger a change in all other records in other tables which are associated. Is there something inbuilt in SQLite to make this happen or do I have to loop through all records in all tables and change them manually?

Upvotes: 0

Views: 61

Answers (3)

Joop Eggen
Joop Eggen

Reputation: 109547

The normal way is to introduce a (hidden to user) primary key, an autoincrement counter, and use that to access the record. UserID or such. Other tables then relate to the user via a UserID.

SQLite uses ROWID aka INTEGER PRIMARY KEY (specific to SQLite).

Upvotes: 1

markus
markus

Reputation: 1651

Why don't you normalize your tables?

You could do:

 table USER:
 UserId(key), name, dateOfBirth

 table OTHER:
 fieldA, fieldB, fieldC, UserId (foreignKey)

That way you don't have to update the other tables accordingly. Depending on your usage scenario this might be the better way to go (e.g. in a traditional transactional setting). If you are in data-warehousing,... it might be better to denormalize your tables

Upvotes: 2

Brandon
Brandon

Reputation: 63

If you have the user's name and DOB set as a foreign key to those other tables, you can also set a cascading trigger in the foreign key. When you create the table with that foreign key, you can set the ON UPDATE attribute to cascade, which will mirror that change in the user's information across any other entries that have it set as the foreign key.

https://www.sqlite.org/foreignkeys.html has more information on the topic.

Upvotes: 1

Related Questions