| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2012-06-11 : 10:01:54
|
| Hi there,In Table1 I have primary key set correspond to foreign keys in table 2. Now I want to remove thoese in parent table1 but keep the corresponding foreign key based rows in table2. See below chart.Table1tbl1_ID | Name 23 | John 35 | SmithTable2tbl1_ID | Score ... 23 | 109 35 | 405 22 | 132 ...Sometimes I need to remove the names and enter new ones but for reason of history I need keep all scores in table2 ...How can I do this? Thanks in advance. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-06-11 : 10:07:51
|
| Why? the foreign key information would be largely useless to you. As you have no idea who the score was for after you remove the entry from table1........Is your use case more complicated than you describe?Transact CharlieMsg 3903, Level 16, State 1, Line 1736:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION http://nosqlsolution.blogspot.co.uk/ |
 |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-06-11 : 10:27:12
|
| Thanks for quick reply.You're right about the key relations but my case is table2 is not used evryday, only once a week to aggregate to get some summarized values to show a graph in report. Then next week, new users' names come in and do the same calculation ... I need to remove the old week's user names but only keep their scores. That's why I need to remove user names in table1 but want to keep scores of these users in table2. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-11 : 15:15:39
|
quote:
Originally posted by allan8964
Thanks for quick reply.You're right about the key relations but my case is table2 is not used evryday, only once a week to aggregate to get some summarized values to show a graph in report. Then next week, new users' names come in and do the same calculation ... I need to remove the old week's user names but only keep their scores. That's why I need to remove user names in table1 but want to keep scores of these users in table2.
in that case why not keep a separate table to track the history of username changes? it can have tb1_ID as fk and against it can have newname and also validity period against them (validfrom,validto)any week you want to get score you can get it from table2 and to get corresponding username at that point go to history table and get current username using validfrom and validto date values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-06-11 : 15:47:39
|
| Thanks visakh16.It's hard to change the database model ... actually this question is about how to remove the rows in table1, which has fk in table2, without touch the fk riws in table2. Is it possible or not? If possible can we run some scripts to reach that goal? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-11 : 15:58:21
|
quote:
Originally posted by allan8964
Thanks visakh16.It's hard to change the database model ... actually this question is about how to remove the rows in table1, which has fk in table2, without touch the fk riws in table2. Is it possible or not? If possible can we run some scripts to reach that goal?
the only way to do it is as follows1. insert new value in master2. repoint fks to newly generated id value in master3. delete the old entry from master------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-06-11 : 16:13:00
|
| Maybe I did not describe it clear. After the table1 rows removed I don't need create relations for new ones inserted in table1 to the old fk in table2. See below:tabe1tb1_ID | Name 100025 | john100036 | smithtable2tb1_ID | Score100025 | 209100036 | 198After I remove the 100025 and 100036 rows in table1 I added other 2, say, 100048, 100059then the table2 looks like:table2tb1_ID | Score100025 | 209100036 | 198100048 | 203100059 | 264If I don't remove the 100048 then just keep it as what it is. If I need to remvoe it then break the restraint to remove it without touching it in table2. I think I can use drop restraint ot WITH NOCHEK to reach this ...Thanks again. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-06-12 : 08:58:16
|
| yes we understood what you wanted to do but we are saying: "that doesn't make sense"It will leave you with records which are totally meaningless (the key that they pointed to no longer exists) so what possible use will the be to you? All you'd have left are orphaned records which are meaningless. They would say:Somebody, Sometime had a score of 209.....And for that you would have to break the referential integrity of the two tables. Permanently.A better idea is to use the audit table suggestion of visakh.You wouldn't have to *change* any existing database tables -- simple add two new tables for the history.We are trying to stop you from doing something we see as just a bad idea.Transact CharlieMsg 3903, Level 16, State 1, Line 1736:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION http://nosqlsolution.blogspot.co.uk/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-12 : 15:01:29
|
| you should probably start reading on referential integrity to understand real purpose of them in relational database------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
How to keep foreign key when parent rows removed?