I have two tables, the second of which contains a field that references the key of the first:
CREATE TABLE [dbo].[Name] (
[id] [uniqueidentifier] DEFAULT newid() NOT NULL PRIMARY KEY ,
[name] [varchar] (64) NOT NULL ,
[referenceId] [uniqueidentifier] NOT NULL,
[createDate] [datetime] DEFAULT getDate() NOT NULL ,
[lastUpdateDate] [datetime] DEFAULT getDate() NOT NULL
) ON [PRIMARY]
;
CREATE TABLE [dbo].[Pronunciation] (
[id] [uniqueidentifier] DEFAULT newid() NOT NULL PRIMARY KEY ,
[pronunciation] [varchar] (96) NOT NULL ,
[nameId] [uniqueidentifier] NOT NULL REFERENCES Name(id),
[createDate] [datetime] DEFAULT getDate() NOT NULL,
[lastUpdateDate] [datetime] DEFAULT getDate() NOT NULL
) ON [PRIMARY]
;
I need to delete an entry in Name and its associated Pronunciations in one transaction so I am trying the following:
Guid aGuid = new Guid(aNameId);
UnitOfWork deleteNameUOW = new UnitOfWork();
deleteNameUOW.AddDeleteMultiCall(new PronunciationCollection(), PredicateFactory.CompareValue(PronunciationFieldIndex.NameId, ComparisonOperator.Equal, aGuid));
deleteNameUOW.AddForDelete(new NameEntity(aGuid));
deleteNameUOW.Commit(new Transaction(IsolationLevel.Serializable, "DeleteName"), true);
The problem is I get a foreign key constraint error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Pronuncia__nameI__4A4E069C'. The conflict occurred in database 'vdialer', table 'Pronunciation', column 'nameId'.
It seems like it always tries to delete the NameEntity before deleting all the pronunciations. If I put the delete of the name entity into another transation it works fine. So how can I do this in one transaction?
I'm using 1.0.2005.1 Final llbl, runtime library 1.0.20051.60621, .net 1.1, vs.net 2003
