Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to insert,update,delete,select record from multiple table having primary key and foriegn key constrain?
Posted

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.
SQL
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
SQL
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
SQL
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


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

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

You might also find the below articles helpful
CRUD Operation in ASP.NET Web Applications Using HTTP Handler and jQuery[^]
Insert, Update, Search and Delete (CRUD operation) using ASP.Net and MySQL[^]
 
Share this answer
 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900