Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Stanley Tan
Starting Member
25 Posts |
 Posted - 2002-09-29 : 08:10:36
|
| Assume Table A has a foreign key to Table B. If a row from Table B (primary key table) is deleted, SQL Server will complain.Is there a way to maintain the relationship between the tables, BUT when a row is deleted from the primary key table, the data is left in the foreign key table? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-29 : 08:23:13
|
| That's normal behavior. The FOREIGN KEY constraint is there to enforce and ensure the integrity of the data in the second table. If you delete a row from the primary, it can make the data in the second table invalid. If you don't care about this, then drop the FOREIGN KEY. |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-09-30 : 09:50:55
|
| if you want to delete a row from the primary key table but leave related row(s) in the foreign key table, you must either change the foreign keys to point to a different, existing primary key, or set them to nullchanging them to some other primary key is definitely application dependent (e.g. customers tied to a salesman who is fired get re-assigned to the oldest salesman still on staff)however, setting foreign keys null is one of the standard behaviours of declarative relational integrity, i.e. ON DELETE SET NULLunfortunately, sql/server does not implement this option, so you would have to write a triggerrudyhttp://rudy.ca/ |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-10-01 : 03:37:16
|
| Thank you two for your replies. Yup, I understand that. Basically, I want to allow the user the ability to delete records but maintain a history of previous transactions. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-10-01 : 03:55:40
|
| Stan,If you want to keep a history of deleted transations, create a new table for this purpose. Don't break your constraints or the databases integrity just for this..DavidM"SQL-3 is an abomination.." |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-10-06 : 23:49:59
|
| Oh, ok, thanks for the reply. It shouldn't slow the system down too much right? |
|
|
|
|
|
|
|
|
Foreign Keys Issue