foreign key constraints

[sogibear]

Hello,

I have two tables, tblStaff and tblSales

I have a foreign key constraint in the form of a one to many relationship from tblStaff to tblSales. Problem is that when i try to delete a record from tblStaff, i get an error message saying that it conflicts with the foreign key constraint.

I know that this IS supposed to happen, but how do i go about deleting a member of staff then ??

Thanks

 

[MeanGreen]

Update all references within tblSales to use a different staff member, and then you can delete staff member from tblStaff. Or you have to delete all records in tblSales with staff member first.

Hope this helps.

 

[JohnDTampaBay]

In order to maintain referential integrity, you won't ever be able to delete a staff member as long as there are sales for that staff member. Two possible solutions that I see:

Virtually delete the member: Add a Status column to tblStaff that denotes Active or Inactive status of each staff member. When a staff member leaves the company, change the status to Inactive.

Assign sales to another staff member, then delete: Update the sales table assigning the old staff member's rows to another staff member. With no rows in tblSales, you can delete the staff member and referential integrity holds.

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[osjohnm]

Hi

Assuming your tables look like this then

STAFF
STAFFID NAME

SALES

SALENO STAFFID

you get the general idea....

delete from sales where staffid = 2
delete from staff where staffid = 2

you first have to delete the data from the child table and then the parent table.

thats the one option or if you go into enterprise manager, right click the Staff table, design table, manage relationships, find the the relationship between staff and sales.

You will notice some options such as 'enforce relationships for replication' etc...

If you 'tick' "Cascade Delete Related Records" all the related records will be deleted in the Sales table if an entry in the Staff table is deleted. You have to 'tick' "Enforce for Update and Insert for the cascade options to be there.

Hope that helps

John

 

[sogibear]

Thanks a lot guy's !!

I think i'll use the active/inactive bit column method !

 

[MeanGreen]

Good idea sogibear. That way you keep your history too.