如何从具有主键和foriegn键约束的多个表中插入，更新，删除，选择记录？ [英] how to insert,update,delete,select record from multiple table having primary key and foriegn key constrain?

问题描述

如何从具有主键和foriegn键约束的多个表中插入，更新，删除，选择记录？

how to insert,update,delete,select record from multiple table having primary key and foriegn key constrain?

推荐答案

对于插入使用 SCOPE_IDENTITY（） [ ^ ]获取主键的值，然后将其插入到具有外键引用的第二个表中。

For Insert use SCOPE_IDENTITY()[^] to get value of the primary key and then insert that into the 2nd table which has a foreign key reference.

BEGIN TRY
  BEGIN TRANSACTION
 
     INSERT INTO Table1 (Col1, Col2) VALUES (Val1, Val2)
     --Declare a variable to store the auto generated Primary key
     DECLARE @Table1_PK INT
     SET @Table1_PK = SCOPE_IDENTITY()
 
     --Insert data into the second table
     INSERT INTO Table2 (Table1_PK, Col3) VALUES (@Table1_PK, Val3)
 

 
  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH

对于更新，请使用唯一的主键值更新记录

For Update use the unique Primary Key value to update the records

BEGIN TRY
  BEGIN TRANSACTION
 
     UPDATE Table1
     SET Col1 = @SomeValue1,
         Col2 = @SomeValue2
     WHERE Id = @Id --Here Id is the Primary key column of table1 and has a 
                    --foreign key reference in table2

    UPDATE Table2
    SET Col3 = @SomeValue3
    WHERE Table1_PK = @Id


  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH

与更新一样，我们使用唯一的主键值删除记录。首先从表中删除具有外键引用的所有记录，然后从主表中删除数据

Like update we use the unique Primary key value to delete the records. First delete all the records from the table that has a foreign key reference and then delete the data from the main table

BEGIN TRY
  BEGIN TRANSACTION
 
   DELETE FROM Table2 WHERE Table1_PK = @Id
   DELETE FROM Table1 WHERE Id = @Id 

  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH

如果要选择记录，请执行 INNER JOIN [ ^ ]或 LEFT JOIN [ ^ 并检索数据

For selecting the records you do a INNER JOIN[^] or a LEFT JOIN[^] and retrieve the data

SELECT * FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.ID = T2.Table1_PK 
--Where clause goes here

您可能还会发现以下文章有用

使用HTTP处理程序和jQuery的ASP.NET Web应用程序中的CRUD操作 [ ^ ]

使用ASP 插入，更新，搜索和删除（CRUD操作） .Net和MySQL [ ^ ]

你应该读这个，



http://www.w3schools.com/sql/default.asp [ ^ ]

You should read this,

http://www.w3schools.com/sql/default.asp[^]

这篇关于如何从具有主键和foriegn键约束的多个表中插入，更新，删除，选择记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！