Hello, I am new to this forum, but I am hoping if someone can help me? I am in the process of developing a new database application, but there is some expertise that I need to bring myself up to speed with.

In particular, I am trying to understand how to create INSERT, UPDATE, and DELETE SQL transactions that would work across multiple tables, but I am somewhat unsure how to do this, while maintaining referential integrity?

Would I for example have to create a View, that would join the tables that I wish to INSERT, UPDATE, and DELETE, and then perform the relevant SQL statement? Or is there another way?


Any help would be appreciated?

Best Regards.

use foreign keys

...with cascading updates and deletes.

Yes, that was implicit in my statement about making joins between tables...so in order to paraphrase my understanding of this:
a. Create a View
b. Using the primary key - foreign key relationships, create table joins linking all the tables to which the new data is to be INSERTed, UPDATEd, or DELETEd...

Is my understanding of this correct?

Do you have an example on the web where you can point me to this?

Best Regards.

Do you have an example that you can show me where this is being used?

What i understand you are saying, is you have to update, insert, or delete all tables where the value occurs to be changed? What about a table join along primary-foreign keys?

Best Regards.

no, a view is nothing more than a stored SELECT query definition, and does not influence relational integrity
first, when writing queries, you can join tables on any columns you want, this is independent of any keys or relational integrity

as for joins used in various sql statements, yes, they are used in SELECTs, but never in INSERTs, and in UPDATEs and DELETEs they are used only if the action against one table is to be determined by the contents of another

No. When you establish foreign keys with cascading referential integrity, changes to the parent record are automatically cascaded to any dependent records. Changes to records in child tables are NOT cascaded upward, though.