February 4, 2009 at 2:57 pm
Hi all,
I'm wondering if there is a way to lock a table so we can prevent the child table records from being deleted..
Thanks in advance.
February 4, 2009 at 3:06 pm
It sounds like you're asking about a "foreign key" constraint. Look those up in Books Online or MSDN, see if that's what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2009 at 10:32 pm
To avoid deletion from child table you need give restrict delete in table properties as per the role. Like, You need add the existing role in the permissions section of the table properties and then configure the access type you require for the table.
Hope this will help to overcome your issue.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
February 5, 2009 at 5:36 am
THanks for your replies. But I think delete restrict will prevent the parent rows from being deleted.
My criteria is when the parent rows are deleted, I dont want the child table records to be deleted..
In Oracle I used ' lock table cri in exclusive mode nowait';
THanks a lot
February 5, 2009 at 5:44 am
Will triggers help the need?
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 5, 2009 at 6:09 am
psangeetha (2/5/2009)
THanks for your replies. But I think delete restrict will prevent the parent rows from being deleted.My criteria is when the parent rows are deleted, I dont want the child table records to be deleted..
In Oracle I used ' lock table cri in exclusive mode nowait';
THanks a lot
As long as you don't have cascading deletes enabled, you don't need to worry about automatic deletes. However, if you have a constraint between the tables, you won't be able to delete the parent until all rows in the constrained child table ARE deleted. If you attempt to delete parent rows, the entire idea behind referential constraints is that you will be stopped because of the constraint.
The only way to really perform this action is to drop the constraint entirely, but then you have no control over data integrity.
Are you sure this what you're trying to do?
By the way, you can simply use a table hint to lock a table as part of a query:
...
FROM dbo.x WITH (TABLOCK) -- or TABLOCKX for exlusive
...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 7:32 am
Thanks again for all your help.
There is a foreign key constraint on the table CRI to parent table ANJ and the application needs the constraint.
For one program, the specification is written such that the program will throw an error when it is unable to delete a record in ANJ table because the records in CRI are not deleted. To test this, we are trying to force the condition so that the program cannot delete the CRI records and so the delete of ANJ records will fail and the program will throw an error.
I'm hoping the lock to the child table in exclusive mode will solve this problem, for testing. Please give me your suggestions.
THank you.
February 5, 2009 at 8:49 am
psangeetha (2/5/2009)
I'm hoping the lock to the child table in exclusive mode will solve this problem, for testing.
No, it won't. If there's a foreign key in place and you try and delete the parent rows then, if cascading delete in on, the child rows will be deleted. If cascading delete is not enabled and there are child rows, then the delete will fail.
If the child table is locked exclusively by another transaction, the delete will wait until the lock is released. It will not only delete the parent rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2009 at 9:02 am
If the child table is locked exclusively by another transaction, the delete will wait until the lock is released. It will not only delete the parent rows.
This is exactly what I need... I just tried
select * from CRI with (tablock) ;
and I opened another query window and ran 'delete from cri where id=7789;'
1 row(s) affected.
But the record got deleted.. Wasn't supposed to wait till the lock is released?
Thank you
February 5, 2009 at 9:10 am
It did wait until the lock was released.
A shared lock is released as soon as the select has finished (in the default isolation level). You either need to do a select with tablock, holdlock, and do that within an uncommitted transaction or you need to use a higher isolation level and run the query within an uncommitted transaction to get the behaviour you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2009 at 9:15 am
Thanks for your input. I think SQL server commits the transaction by default on the management studio when I run the select query:
select * from invntry_adj with (tablock,holdlock)
How is it possible to uncommit the transaction until I run the delete query?
Thanks so much
February 5, 2009 at 10:11 am
Run the query with an explicit open transaction statement:
BEGIN TRANS
...
Don't put in a commit until you're complete with your testing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply