Deleting primary key when no foreign records exist?

Is there a trigger that would handle this situation? I have a 1 to
many between two tables and would like the primary key deleted if the
last foreign record is deleted.
Thanks in advance!Sure, in an AFTER trigger on the child table, something like (including a
scenario to try it out.):
set nocount on
go
create table parent
(
parentKey int primary key
)
create table child
(
childKey int primary key,
parentKey int foreign key references parent(parentKey)
)
insert into parent
select 1
union all
select 2
union all
select 3
insert into child
select 1,1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
go
create trigger child$deleteTrigger
on child
after delete
as
--be sure to add error handling
delete parent
--this gets all parent rows that are related to the deleted child rows based
on the migrated key from the parent
where exists (select 1
from deleted
where parent.parentKey = deleted.parentKey)
--this excludes parents where a child still exists
and not exists (select 1
from child
join deleted
on child.parentKey =
deleted.parentKey
where parent.parentKey = deleted.parentKey)
go
delete child where parentkey = 1
select *
from parent
left outer join child
on parent.parentKey = child.parentKey
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1139625347.478136.146410@.g14g2000cwa.googlegroups.com...
> Is there a trigger that would handle this situation? I have a 1 to
> many between two tables and would like the primary key deleted if the
> last foreign record is deleted.
> Thanks in advance!
>|||Many thanks Louis!|||Yes, you can do this with a trigger, if you like to write procedural,
proprietary code. Look for a COUNT(*) = 0 in a PK-FK join.
Another way to do this is to put one "child" item the same table as the
"parent" since you seem to require at least one "child" as part of the
design. When you delete the "only child", you have to delete the
parent.
Oh, did I mention that the code is messy?|||> Another way to do this is to put one "child" item the same table as the "p
arent"
Joe,
Are you saying your solution conforms to 3NF?
What if later you'll need to delete the child row stored along with the
parent? You'll have to move another row from the child table to the
parent one. Are you claiming it's less messy than Louis's solution?
Also instead of selects against the child table you'll have to select
against a union of the child and the parent, right?
How would you enforse a unique constraint on the child?sql

Deleting foreign key record

Hi all,.

I have a table Student and the primarykey is studid..

This studid is the foreign key in another table called Class.

I want to delete the records in the table Class...

How can i do it..

Please help me...

Regards,

Mathewyou'll need to drop the foreign keys between the two tables delete the required data, and add the foriegn keys once again.|||

Quote:

Originally Posted by jamesd0142

you'll need to drop the foreign keys between the two tables delete the required data, and add the foriegn keys once again.

Hi James,

Is that the only method to delete those records??

Regards,
Mathew|||What error are you getting when you try to delete these records?

this will help me determine if i have suggested the correct method...

because you shouldnt really get any issues deleting a row frm the table 'class'

i can see y u wud get an error deleting a record from Student table however.|||

Quote:

Originally Posted by jamesd0142

What error are you getting when you try to delete these records?

this will help me determine if i have suggested the correct method...

because you shouldnt really get any issues deleting a row frm the table 'class'

i can see y u wud get an error deleting a record from Student table however.

Hi all,

I didnt try to delete record.. i have to do it,, before that i would like to know whether it is possible or not....

regards,
Mathew|||

Quote:

Originally Posted by mathewgk80

Hi all,

I didnt try to delete record.. i have to do it,, before that i would like to know whether it is possible or not....

regards,
Mathew

Ok, well as i said above, i can's see any problems with deleting from the 'class' table, although you might have a problem deletiung from the 'student' table because;

The studid field in the 'class' table would no longer have a lookup, so droping the foriegn keys first would allow you to delete a record in 'student', although if you have a value in the 'class' table for studid thats not in the 'student' table you 'shouldnt' be able to create the foriegn keys once again...

i hope you understand my logic here :ssql

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
Ryan
Create an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>
|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>
sql

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hi All,
>> If I delete a field in SQL Server, will all constraints, foreign keys,
>> indices etc. associated with that field also be removed?
>> Thanks in advance
>> Ryan
>>
>

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>

Deleting all without contraints

I have a table that has about 10 different foreign key and trigger
contraints. I am wanting to setup a delete that will delete records
that do not have a contraint problem.
The problem is that once it hits one record with a contraint problem it
stops I want it to continue on to the next record.
I first tried this:
DELETE FROM tblContact
WHERE sActiveFlag = 'F'
I then tried a cursor thinking I could check the error and continue but
it still stops:
DECLARE @.lContactId int
DECLARE cExchange SCROLL CURSOR FOR
select lContactId FROM tblContact
WHERE sActiveFlag = 'F'
order by lContactId
FOR READ ONLY
OPEN cExchange
FETCH FIRST FROM cExchange INTO
@.lContactId
WHILE @.@.FETCH_STATUS = 0
BEGIN
DELETE FROM tblContact
WHERE lContactId = @.lContactId
IF ( @.@.ERROR != 0 )
BEGIN
FETCH NEXT FROM cExchange INTO
@.lContactId
END
ELSE
BEGIN
FETCH NEXT FROM cExchange INTO
@.lContactId
END
select @.@.FETCH_STATUS, @.@.error
END
CLOSE cExchange
DEALLOCATE cExchange
Any help would be greatly appreciated.
Thanks,
DeidreDS wrote:
> I have a table that has about 10 different foreign key and trigger
> contraints. I am wanting to setup a delete that will delete records
> that do not have a contraint problem.
I suppose it's out of the question to just go through each constraint,
figure out what it means, and translate it into a WHERE subclause?
DBCC CHECKCONSTRAINTS('ttOrdClubItem') WITH ALL_CONSTRAINTS,
ALL_ERRORMSGS
may help.|||Thanks for the suggestion. I was trying not to do that because there
are so many contraints and some are around databases. Also this tables
has the potential to have alot more contraints added so I didn't want
to miss anything. Any other suggestions?|||Did you try disabling triggers and constraints?
alter table t1
nocheck constraint all
go
alter table t1
disable trigger all
go
delete ...
alter table t1
check constraint all
go
alter table t1
enable trigger all
go
AMB
"DS" wrote:

> Thanks for the suggestion. I was trying not to do that because there
> are so many contraints and some are around databases. Also this tables
> has the potential to have alot more contraints added so I didn't want
> to miss anything. Any other suggestions?
>|||If I disable the triggers and constraints wouldn't it then allow me to
delete the ones with contraints? I don't want to delete the ones with
contraints I want to skip those.
Deidre|||DS wrote:
> Thanks for the suggestion. I was trying not to do that because there
> are so many contraints and some are around databases. Also this tables
> has the potential to have alot more contraints added so I didn't want
> to miss anything. Any other suggestions?
After thinking it over some more, I realize that the DBCC command I
gave above is totally not what you want. You want to know which records
can be deleted without violating constraints; the DBCC command would
return which records are violating current constraints. Not the same
thing at all.
The only server-side solution I can think of is to use sp_execsql in a
cursor or some other loop:
DECLARE @.IContactId int
DECLARE @.returnStatus int
DECLARE cExchange SCROLL CURSOR FOR
select lContactId FROM tblContact
WHERE sActiveFlag = 'F'
order by lContactId
FOR READ ONLY
OPEN cExchange
FETCH FIRST FROM cExchange INTO @.lContactId
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXECUTE @.returnStatus = sp_executesql
N'DELETE FROM tblContact WHERE lContactId = @.theID',
N'@.theID int',
@.IContactId
-- if @.returnStatus is 0, delete succeeded; if not, it's 1.
-- you could do something with that fact now, if needed
FETCH NEXT FROM cExchange INTO @.lContactId
END
Short of that, you'd have to do your looping from an external program.|||Good suggestion! I really thought that was going to work but it still
stops once it hits the first constaint problem. Ugh!!!

Deletes

I want to clarify something regarding the deletes. On Publisher, we have a
table which has foreign keys. I checked the same table in subscriber. It
does not have foreign keys but it has primary and unique keys. Does
Replication ensure that delete happen in the same order on all subscribers as
they do in Publisher otherwise we can end up with orphans in subscribers.
For deletes, we are replicating the execution of delete stored procedure.
Can someone please clarify this?
Thanks
For transaction, yes it does as long as cascading updates and deletes are
enforced for replication.
With merge it is best to make the constraints not for replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:F2C6E011-F9BF-4DC6-9346-71563E93E25D@.microsoft.com...
>I want to clarify something regarding the deletes. On Publisher, we have a
> table which has foreign keys. I checked the same table in subscriber. It
> does not have foreign keys but it has primary and unique keys. Does
> Replication ensure that delete happen in the same order on all subscribers
> as
> they do in Publisher otherwise we can end up with orphans in subscribers.
> For deletes, we are replicating the execution of delete stored procedure.
> Can someone please clarify this?
>
> Thanks
>

deleted all records strangely

we have two tables: TIMECARDBATCH and TIMECARD. TIMECARD has a foreign key
to TIMECARDBATCH.
User can only delete one row from TIMECARDBATCH at a time. We have a delete
trigger on TIMECARDBATCH which deletes corresponding records from TIMECARD.
In our production environment at one time all records in TIMECARD gets
deleted but there are still records in TIMECARD. Now we look into the code
and the programmer whoe code the delete trigger has this code:
CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
FOR DELETE
AS
declare @.batchNumber int
declare @.id int
select @.batchNumber = batchNumber
from deleted
declare c_tc cursor local for
select id from timecard where batchNumber=@.batchNumber
open c_tc
fetch c_tc into @.id
while @.@.fetch_status=0
begin
delete from timecard where id=@.id
fetch c_tc into @.id
end
What I can see wrong here is that the cursor "c_tc" does not get closed and
deallocated. Can this cause the problem?
Thanksone more question i have is :
does SQL Server log SQL statements for a database? If yes then where can i
find them?
Thanks
"JY" <jy1970us@.yahoo.com> wrote in message
news:BVKef.2604$w84.470302@.news20.bellglobal.com...
> we have two tables: TIMECARDBATCH and TIMECARD. TIMECARD has a foreign key
> to TIMECARDBATCH.
> User can only delete one row from TIMECARDBATCH at a time. We have a
> delete trigger on TIMECARDBATCH which deletes corresponding records from
> TIMECARD.
> In our production environment at one time all records in TIMECARD gets
> deleted but there are still records in TIMECARD. Now we look into the code
> and the programmer whoe code the delete trigger has this code:
> CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
> FOR DELETE
> AS
> declare @.batchNumber int
> declare @.id int
> select @.batchNumber = batchNumber
> from deleted
> declare c_tc cursor local for
> select id from timecard where batchNumber=@.batchNumber
> open c_tc
> fetch c_tc into @.id
> while @.@.fetch_status=0
> begin
> delete from timecard where id=@.id
> fetch c_tc into @.id
> end
>
> What I can see wrong here is that the cursor "c_tc" does not get closed
> and deallocated. Can this cause the problem?
> Thanks
>|||The trigger is poorly written. It does not take multi-row deletes into
consideration. It also uses a cursor which is not needed. ( Not deallocating
the cursor does not seem the issue here, but in general it can cause the
cursor reference to be retained in the memory and the data structures
comprising the cursor are not released. )
Just change the trigger as:
CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
FOR DELETE
AS IF @.@.ROWCOUNT = 0 RETURN
DELETE FROM timecard
WHERE EXISTS (
SELECT *
FROM deleted d
WHERE d.batchNumber = timecard.batchNumber )
Anith|||>> does SQL Server log SQL statements for a database?
Not unless you explicitly track them, perhaps using profiler trace or some
proprietary methods.
Anith|||Hi Anith,
Thanks for your reply. Yes this trigger is written poorly and your solution
is excellent.
But our client wants to know why it happened with the current trigger we
have and the only thing i can think of is that the programmer forgets to
close the cursor. I just want to confirm if this can be the cause in any
case?
Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ehZJX%23t6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The trigger is poorly written. It does not take multi-row deletes into
> consideration. It also uses a cursor which is not needed. ( Not
> deallocating the cursor does not seem the issue here, but in general it
> can cause the cursor reference to be retained in the memory and the data
> structures comprising the cursor are not released. )
> Just change the trigger as:
> CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
> FOR DELETE
> AS IF @.@.ROWCOUNT = 0 RETURN
> DELETE FROM timecard
> WHERE EXISTS (
> SELECT *
> FROM deleted d
> WHERE d.batchNumber = timecard.batchNumber )
> --
> Anith
>|||>> But our client wants to know why it happened with the current trigger we
Based on the code, it looks like the following line of code might be the
culprit:
select @.batchNumber = batchNumber
from deleted
This will always return only one value and the code within the cursor will
always be executed only once.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uVeC9au6FHA.4076@.tk2msftngp13.phx.gbl...
> Based on the code, it looks like the following line of code might be the
> culprit:
>
> select @.batchNumber = batchNumber
> from deleted
> This will always return only one value and the code within the cursor will
> always be executed only once.
> --
> Anith
>
Hi Anith,
The user from the browser can only delete one TIMECARDBATCH record at a
time. So "select @.batchNumber = batchNumber from deleted" is ok as there is
one row to get deleted.
What do you think about forgetting to close the cursor? Can it be a problem
when multiple users are deleting data?
Thanks|||I suspect there is actually an unhandled error due to the FK.
Try deleting a timecardbatch row directly in SQL Query Analyzer and see
if you get an error.
The most solid solution is to change the fk in TIMECARD to cascade
deletes. [and drop that horribly written trigger :) ]
JY wrote:
> we have two tables: TIMECARDBATCH and TIMECARD. TIMECARD has a foreign key
> to TIMECARDBATCH.
> User can only delete one row from TIMECARDBATCH at a time. We have a delet
e
> trigger on TIMECARDBATCH which deletes corresponding records from TIMECARD
.
> In our production environment at one time all records in TIMECARD gets
> deleted but there are still records in TIMECARD. Now we look into the code
> and the programmer whoe code the delete trigger has this code:
> CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
> FOR DELETE
> AS
> declare @.batchNumber int
> declare @.id int
> select @.batchNumber = batchNumber
> from deleted
> declare c_tc cursor local for
> select id from timecard where batchNumber=@.batchNumber
> open c_tc
> fetch c_tc into @.id
> while @.@.fetch_status=0
> begin
> delete from timecard where id=@.id
> fetch c_tc into @.id
> end
>
> What I can see wrong here is that the cursor "c_tc" does not get closed an
d
> deallocated. Can this cause the problem?
> Thanks
>

Delete/truncate table ignoring contraints

Is there any easy way to truncate a table which has a foreign key restraint? I want to override the default behavior which is to not allow truncate of parent tables. I want to be able to temperarily remove the contraint so I can truncate the temple, how do you do this?

I should add that the systables keep track of the contraints. There should be a query that I could run that would just disable the checking of the contraint? Any help?|||No. There is no way to control/alter the behavior of truncate table. If you need to use it you have to drop the FK constraints even if the table is empty.|||

O.K. So... that isn't easily done.

Is there any easy way to simply copy the schema, contraints and everything but simply no data? Perhaps using DTS?

Or perhaps, is there another way to reset the identity information of a table? The delete operator does not reset identity.

|||If you want to reset identity value you can use DBCC CHECKIDENT. See Books Online for more details. Your original question was different.||| your original question was different

:) Thanks.

DELETE/TRUNCATE a table of 10,000 records is taking more than a minute

Hi everyone,
I have a table of 10,000 ++ records with no links keys or foreign
constraints; but a truncate table or a delete from takes more than a
minute to execute or the query analyser just hangs but the timer @. the
bottom corner is running.
What would cause it to take so long to truncate a table?
Please advise.
ThanksCan you check (sp_who2) to see if the process is being bklocked. If so you
can use dbcc inoutbuffer (spid) to see what the blocking process is doing.
HTH,
Paul Ibison

delete w/ foreign key question

Is there a way to see the locks associated with a delete statement on a table (tab1) that has 5 or 6 Foreign key relationships. Trying to understand the impact of the delete on concurrency There are several delete deadlocks on one of the foreign key tables (tab2) and the system does not delete from the table (tab2) that is throwing the delete deadlock error. Wondering about the impact of foreign keys on deletes on Tab1 on Tab2.

Didn't see anything in query analyzer that would show the actual locks. It seems to have scans or such but no lock levels etc are shown.

Does anyone have any knowledge of how to see the actual locks thrown by a given statement. The delete on Tab1 statement is very quick so using EM has proved fruitless.

MikeOh, such simple question but resolving blocking/deadlocking is really an art. You might want to start here.

http://support.microsoft.com/kb/224453

DELETE statement conflicted

Hello

I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.

However, when I tried to delete a row in the first table I saw this error:

DELETE FROM [dbo].[Names_DB]
WHERE [LName_Name]=N'andrews'

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.

I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.

Please advise.

I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.

Thanks

Regards

Lynn

Have you enabled the Cascade Delete Related Records for all the relations of the maintable and related tables etc? I think you have missed it somewhere.|||

Hello Fredrik

Thanks for the swift reply.

No I have not enabled the Cascade Delete Related Record I didn't know this was required, I am still a novice. Yes I have certainly missed this.

Now that I understand that this is required, I need to learn how to do this. Can you please direct me to a suitable turorial or perhaps explain how this is carried out and what is the required code for this process?

Thanks

Lynn

|||

When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.

Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)

|||

Fredrik N:

When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.

Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)

Hello Fredrik

Within table properties where do I enable the delete to the existing tables. Can I add update also?

Thanks

Lynn

|||

Hi

You can add constrains to a table it could be something like this:

CREATE TABLE Books ( BookIDINTNOT NULLPRIMARY KEY, AuthorIDINTNOT NULL, BookNameVARCHAR(100)NOT NULL, PriceMONEYNOT NULL)GOCREATE TABLE Authors ( AuthorIDINTNOT NULLPRIMARY KEY,Name VARCHAR(100)NOT NULL)GOALTER TABLE BooksADD CONSTRAINT fk_authorFOREIGN KEY (AuthorID)REFERENCES Authors (AuthorID)

ON DELETE CASCADE

ON UPDATE CASCADEGOYou can take a look atDatabase Objects: Constraints for more

Hope this helps.|||

Hi Thanks for the post and the info.

As I have already created tables I am a little wary about altering tables in case I loose the data.

The tables I have already have foreign keys and primary keys.

I have a parent table called: DomNames

Primary Key = DomNamesID

I have recently inserted a foreign key:

Foreign Key = CatA_ID (taken from the Catagory A child table)

Then I have child category tables from A - Z

In category table A

Primary Key = CatA-ID

Foreign Key = DomNamesID (taken from the DomNames table)

In category table B

Primary Key = CatB-ID

Foreign Key = CatA_ID (taken from the previous A category table)

Each following table uses the Category ID alphabetical letter as a primary key and the previous tables Category ID as the foreign key, each table has a DomNameID column.

All tables contain data.

Do I miss out the first section of the code you mentioned and just put this:

ALTER TABLE DomNames
ADD CONSTRAINT fk_CatA_ID
FOREIGN KEY (CatAID)
REFERENCES DomNames (DomNamesID)

ON DELETE CASCADE

ON UPDATE CASCADE

I would be grateful if you would confirm before I alter my database.

Thanks

Regards

Lynn

Delete problem

Dear all,

I have an asp.net webform which will provide delete function. If there are foreign key constraint and the user click the delete button, i would like the user to get response (Eg You must delete other data first......or something like this)

1. Any good idea?

2. One way i search from this form is like this, it raise error in db side(stored proc)

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR (''VersionA.)
RETURN
END

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR (''VersionB.)
RETURN
END

How can i get the raiseerror and identiy the veriosn of error in asp.net page?

Thanks in advance!!!!

Return a value to indicate that child records exists. Raising an error is not good idea due to performance.

|||This should already be taken care of from the sql side as long as youhave enforce constraits on for deleting and crud operations. Allyou have to do is catch the SQL errors and handle them from your aspxwhich isnt hard. Search handling sql errors from the codebehind. All you would have to do is handle the event and showjavascript to the user that you need to delete things.