I have a class Product, which might be used in OrderItems, which are invidual lines of an Order. I want to ensure that a Product cannot be deleted if it is used in an OrderItem. How do I do this? At present I do not have a one-to-many relationship between Product and OrderItem (e.g. a Set) indicated in my mapping files, because I've not needed to view a set of OrderItems based on their related 'one' Product. Do I need to do this in order to enforce referential integrity this way?

Sounds like what you want is a FK from OrderItems to Product (OrderItems is an associative table between Order and Product).

Are you using SchemaExport to define your schema based on the mapping files? In your OrderItem mapping file, if you define a many-to-one association to Product, SchemaExport will create a FK constraint. You will not be able to delete Product records from the database that are referenced by OrderItem records.

No one-to-many is necessary in the Product mapping file, as far as I know. That is just a convenience association, and does not affect the database schema.

I have this defined in my mapping file for OrderItem:

<many-to-one
name="product"
class="testing.om.Product"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="`product_id`"
/>

In the schema generated, I end up with this:

alter table `order_item` add index FK2D110D64AC37E19E (`product_id`), add constraint FK2D110D64AC37E19E foreign key (`product_id`) references `product` (`id`);

So, that would appear to suggest I'm doing what's necessary, wouldn't it? I think I know what the answer might be, though. I'm using MySQL and I'm not sure that it's set up on the test rig to use InnoDB tables. I'm pretty sure that if it uses MyISAM tables, there's no foreign key constraint checking anyway, is there? So, am I right in thinking that this could be a problem at the db level?

I haven't used MySQL, but I think InnoDB stuff is related to transaction support, not constraints. I could be wrong.

Just test it out directly in the db - see if it lets you delete a product record if there is a order_item record referencing it.

You are wrong in this case. You need to use InnoDB tables for foreign key constraints. Though the documentation (of MySQL) states that in the future foreign key constraints will become available for MyISAM and other storage engines as well.

See: http://dev.mysql.com/doc/mysql/en/ansi- ... -keys.html

Ensuring that the table type was InnoDB, not MyISAM, did in fact sort this out - I was already doing all that was needed in Hibernate, it's just that the database wasn't doing its bit.

Incidentally, for anyone else who runs into this issue, changing over isn't quite so straightforward as you might imagine. Simply changing the engine type to InnoDB doesn't buy you anything - you have to do a bit more. The reason is that MyISAM has no knowledge of foreign key constraints, so even though your Hibernate schema export may contain them, MySQL will have lost them. What I found you had to do, eventually, was this:

1. Do a dump of your existing database. Ideally it should be data only, but I did the whole thing and then did a grep for 'INSERT INTO' (I wanted a full copy in case everything screwed up).

2. Ensure that my.ini is now set with InnoDB as default.

3. Drop the database.

4. Create the database again.

5. Use the Hibernate schema export to create the schema, which will now have InnoDB tables and all the foreign key constraints.

6. In the mysql command-line client, call SET FOREIGN_KEY_CHECKS=0;

7. Load the data dumped in stage 1 (stage 6 is necessary to stop MySQL complaining about inconsistencies while you're bulk loading).

8. Call SET FOREIGN_KEY_CHECKS=1; to make sure it does its checking now the data is loaded.