Is it possible to have a single fk in a table that is a pk from more than one table?

IE, you have a demographics table, user table, and a company table.

The demographics table contains info about both the user and the company, and instead of having both a userId and CompanyId foreign keys, could we have one field that might be either the companyId or the userId? And have another field that would identify the demographic type?

How would you implement that?

thanks.

I think you'll find that there aren't any databases that support creating a foreign key that references more than one primary key (although you could fake it by manually doing the validation in a trigger).

I would do the following:
- Remove the userId and companyId from the demographics table.
- Add a couple of tables: person_demographics and company_demographics. Each table would have two 'id' columns, one a foreign key to the demographics table, and the other a foreign key to the respective type table (i.e. person or company).

If you still want a single demographics table that you can query without having to worring about the new tables, then create a view.

Actually I was able to create two imported keys in the table in MySQL. And they both reference the same ownerId field.

My problem is writing the annotations to have hibernate generate the tables and the persistence.

If I only had to do this all in sql and db scripts, I'd be done by now, but I'm trying to understand hibernate. :-)

do a HQL query to join your objects and do not map the relationship.
The relationship you describe can be mapped in Hibernate through <any> but it requires an extra discr column and has some conceptual/perf limitations. Plus it is not mappable through annotations so far (only hbm files)

I have a somewhat similar situation; trying to create a CMA which associates metadata with data in an existing database, probably a database other than the one where the metadata is stored and which may be legacy, so may have composite primary keys,

The solution I've come up with so far is to use the fact that Hibernate likes to use a class to make up composite keys. So I have a "serializable" column on my metadata table which takes a serialized object, and an "entity" column that takes the name of the entity. Then I can simply do a session.load(entity, serializable) to retrieve the data.

It's nice in that it imposes no limits on the legacy database; but I haven't got as far as working out what the cost is in performance terms. Potentially quite a lot, I fear!