Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Exclamation INSERT, UPDATE, and DELETE SQL operations while maintaining referential integrity..?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FlashJack View Post
    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?
    use foreign keys

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...with cascading updates and deletes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2010
    Posts
    3
    Quote Originally Posted by r937 View Post
    use foreign keys

    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.

  5. #5
    Join Date
    Nov 2010
    Posts
    3
    Quote Originally Posted by blindman View Post
    ...with cascading updates and deletes.
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FlashJack View Post
    a. Create a View
    no, a view is nothing more than a stored SELECT query definition, and does not influence relational integrity
    Quote Originally Posted by FlashJack View Post
    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...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by FlashJack View Post
    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?
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •