CODEWITHSUNDEEP
c#asp.netsql-server
el323
el323

Reputation: 2920

How to delete a row being referenced in another table?

The question title might seem a bit vague. I am going to explain it here.

So, I have two tables: 1) Event 2) Contact

Event table has a foreign key (ContactID) referenced from Contact Table. So when I delete a contact I have to first delete the event row which is using the contactID being deleted, to avoid any conflicts.

But I dont want that. Actually I use ContactID to get ContactName and ContactNumber while displaying Event details in a gridview along with other columns from event table.

This is the reason I dont want the event to be deleted I just want Null values for ContactName and ContactNumber.

I have tried setting ContactID as NULL in Event Table. Code:

string query0 = "Update Event SET ContactID=@contact where ContactID='" + cntID + "'";
SqlCommand cmd0 = new SqlCommand(query0, conn);
cmd0.Parameters.AddWithValue("contact", DBNull.Value);
conn.Open();
cmd0.ExecuteNonQuery();
conn.Close();

This sets ContactID in Event Table to NULL just fine. But when I try to get Event Information in a gridview and apply a join on both tables I dont get the Event record where I set ContactId to NULL. This is the query to get event Info:

SELECT Event.ID, Event.StartDate, Event.Title, Contact.Name, Contact.Cell, Event.EventType, Event.Confirmed, Event.Comments 
FROM Event 
INNER JOIN Contact ON Event.ContactID = Contact.ID

What is the workaround for this? kindly help me and guide me in the right direction.

Upvotes: 1

Views: 92

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

Your issue with the final query is that you're using INNER JOIN which will only return results that exist in both tables. If you have events without a relevant contact then change it to a LEFT JOIN;

SELECT e.ID, e.StartDate, e.Title, c.Name, c.Cell, e.EventType, e.Confirmed, e.Comments 
FROM Event e
LEFT JOIN Contact c ON e.ContactID = c.ID

I've also updated the query to use table aliases.

Upvotes: 1

Related Questions