I'm trying to apply a database constraint to enforce the idea that a "dateDeleted" column must be filled in, or there must be a foreign key present. The problem is, Hibernate is violating the constraint as soon as I create a new object of this type because it goes back and updates the row with the foreign key after it has already inserted it.

This is the (SQL Server) constraint:

alter table entries add constraint deleted_or_product check(datedeleted is not null or (datedeleted is null and productid is not null and productidx is not null))

This is what I see Hibernate doing when I attempt to save a new Entry:

insert into Entries (uuid, dateDeleted, displayName, active, assetsOnHold,
chartsOnHold, copyOnHold, lastUpdateName, lastUpdateDate, creationDate,
duplicatedFrom, legacyId, metadataId, catalogId)
values (’ab9ce535-bdf7-4f65-a582-637c34b4e60e’,NULL,’test3′,1,0,0,0,
‘Widen Development’,'Aug 10 2010 4:06:33:770PM’,
‘Aug 10 2010 4:06:33:770PM’,NULL,NULL,’233′,’1′)

update Entries set productId=7, productIdx=2 where id=10

The interesting thing is, this works fine if I add not-null="true" to the collection mapping key:

<list name="entries" lazy="true" cascade="all">
<key column="productId" not-null="true"/>
<index column="productIdx" />
<one-to-many class="collective.cp.model.Entry" />
</list>

With that mapping, Hibernate assigns the foreign key as part of the insert statement:

insert into Entries (uuid, dateDeleted, displayName, active, assetsOnHold,
chartsOnHold, copyOnHold, lastUpdateName, lastUpdateDate, creationDate,
duplicatedFrom, legacyId, metadataId, catalogId, productId, productIdx)
values (’e49d6175-9761-4ed8-b6ff-33be0028abba’,NULL,’test4′,1,0,0,0,
‘Widen Development’,'Aug 10 2010 4:12:57:617PM’,
‘Aug 10 2010 4:12:57:617PM’,NULL,NULL,’234′,’1′,’7′,3)

The problem with that, however, is we can't have a not-null constraint on the foreign key. It is valid for the foreign key to be null if dateDeleted is filled in.

Is there any way to force Hibernate to assign the foreign key as part of the insert statement without using a not-null constraint?

Thanks for any suggestions.