Hibernate version:
1.2GA
I have been tasked with trying to port our working web app from Oracle to SQL Server 2005. This app is currently working and stable with NHibernate. To begin the migration, I used Microsoft's SSMA (SQL Server Migration Assitant) which did a good job of converting the schema and data to MSSQL, and once I had changed all the Oracle sequences to MSSQL identity columns, the entire app was once again working on the new db...
Except: now I am getting foreign key errors when deleting a record that I wasn't getting on the Oracle version.
For example, I have a Document class (which maps to a table called "Library"). Every document record can have a list of links to other "Associated Documents". This is done with a table called "r_library_library." The schema looks like this:
Code:
Library table:
ID - primary key
TITLE, etc - data fields
R_Library_Library table:
ParentLibraryId - foreign key to Library table
ChildLibraryId - another foreign key to Library table
The SQL to create this is
Code:
CREATE TABLE
LIBRARY
(
ID int IDENTITY NOT NULL,
TITLE varchar(max) NOT NULL,
-- plus other fields
)
GO
CREATE TABLE
R_LIBRARY_LIBRARY
(
PARENTLIBRARYID int NOT NULL,
CHILDLIBRARYID int NOT NULL
)
GO
ALTER TABLE LIBRARY
ADD CONSTRAINT PK_LIBRARY
PRIMARY KEY
CLUSTERED (ID ASC)
GO
ALTER TABLE R_LIBRARY_LIBRARY
ADD CONSTRAINT SYS_C0034348
FOREIGN KEY
(CHILDLIBRARYID)
REFERENCES
LIBRARY (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
ALTER TABLE R_LIBRARY_LIBRARY
ADD CONSTRAINT SYS_C0034347
FOREIGN KEY
(PARENTLIBRARYID)
REFERENCES
LIBRARY (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
And the Mapping:
Code:
<class name="Library" table="library">
<id name="Id" column="id" type="int">
<generator class="identity" />
</id>
<property name="Title" column="title" />
<!-- other fields ... -->
<bag name="AssociatedDocuments" table="R_LIBRARY_LIBRARY">
<key column="ParentLibraryId" />
<many-to-many class="LibrarySearchSummary" column="ChildLibraryId" not-found="ignore" />
</bag>
</class>
I'm pretty stumped as to how to proceed. I could just get rid of the foreign key, which seems like the lazy way out. I could change the ON DELETE NO ACTION to something else, but that's not much better than no foreign key, and wasn't how it worked in Oracle. Or I could change the mappings, but I don't know how.
Any help please?
Login
FAQ
Search