Violation of foreign key constraint while inserting a new itemNo

Question

User-1993844047 posted

CREATE TABLE Approvals (
  App_ID int NOT NULL IDENTITY,
  ItemNo int NOT NULL 
	REFERENCES IssueLog(ItemNo)
		ON UPDATE CASCADE
		ON DELETE NO ACTION,		 
  AppType int NOT NULL,
  ByWhom int DEFAULT NULL,
  ApprovalDate date DEFAULT NULL,      
  CONSTRAINT PK_Approvals PRIMARY KEY NONCLUSTERED ([App_ID], [ItemNo])      
); 

// get the generated item no                 
Int32 ItemNo = (Int32)e.Command.Parameters["@NewItemNo"].Value;

Approvals app = new Approvals(); 
  
SqlConnection conn = new SqlConnection();                
conn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=RCS_2_02;Data Source=localhost\\SQLEXPRESS";
SqlCommand command = new SqlCommand("insert into Approvals (ItemNo, AppType, ByWhom, ApprovalDate) VALUES (@ItemNo, @AppType, @ByWhom, @ApprovalDate)", conn);
command.Parameters.AddWithValue("@ItemNo", ItemNo);

// Enter Issue Log Type
command.Parameters.AddWithValue("@AppType", 1);
command.Parameters.AddWithValue("@ByWhom", Convert.DBNull);
command.Parameters.AddWithValue("@ApprovalDate", Convert.DBNull);


conn.Open();
command.ExecuteNonQuery();
conn.Close();

Should I avoid the cascade update clause on my create db statement?

Because it triggers a foreign key violation here when I am inserting a brand-new itemNo

Thanks

Jack

Answer 1

User1577371250 posted

Hi,

The New ItemNo you are trying to INSERT here should be present in IssueLog Table.

If you don't have the ItemNo then you will get the Foreign Key Violation Exception.

Answer 2

User-1993844047 posted

Hello there,

Thanks for your prompt reply.

I am putting this code in the FormView_Inserted method if that matters?

If I understand asp.net correctly, the log is inserted before the approval is inserted, .... or not?

Thanks

Jack

Answer 3

User1577371250 posted

Hi,

1. Yes that will do the Job. First you need to Insert the ItemNo to the table.

2. Then you can INSERT the data to Approvals table

Answer 4

User1918509225 posted

Hi lucky7456969,

Thanks for your post here.

The ItemNo in your Approvals  is the foreign key in your IssueLog, so you need to first check if it exist in the IssueLog Table before your insert the new ItemNo  in the IssueType.

Please refer to the tutorial in the link below:

http://www.w3schools.com/sql/sql_foreignkey.asp

Best Regards,

Kevin Shen.