Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL design around lack of cross-database foreign key references

Tags:

For better or worse, we have a solution that relies on multiple databases that all reference a common administration database. Databases ship as part of modules, and not all modules are required for an installation (probably why we have multiple databases in the first place). The admin database is required, however ... so it will always be there.

I would like to bring some referential integrity and order to the chaos, but am stymied by SQL server's inability to do cross-database foreign keys. There is NOT a lot of churn in the database, but information will be inserted/updated by (ahem) non-technical users.

My choices as I see them are:

a) Impose pseudo foreign key using triggers (ok, but a bit of work)

b) Use triggers to replicate from admin to other databases (a clear recipe for disaster)

c) Impose psuedo foreign key in code / DAL (does not play well with ORM)

d) Don't worry about it at DB level, use good UI design to make sure no one does anything stupid and restrict access/hold breath on direct SQL access.

Frankly, I'm inclined to go with "D", but figured I'd go out for opinions smarter than me ...

like image 634
brmore Avatar asked Nov 05 '08 17:11

brmore


People also ask

Can we do data design without foreign key?

You could instead configure two tables such that one refers to the other, but without any defined foreign key. For instance, let's say you have a Customer table with its own ID column, and you have a User table with its own ID as well. You could add a UserId column to Customer and still use it for querying purposes.

What happens if there is no foreign key in SQL?

The obvious problem with the lack of foreign keys is that a database can't enforce referential integrity and if it wasn't taken care of properly at the higher level then this might lead to inconsistent data (child rows without corresponding parent rows).

What happens to foreign key references when a table is dropped?

When you drop a foreign key using the DROP FOREIGN KEY clause of the ALTER TABLE statement, Db2 drops the corresponding referential relationships. (You must have the ALTER privilege on the dependent table and either the ALTER or REFERENCES privilege on the parent table.)


1 Answers

We have the exact same problem and quite frankly, it sucks. Our only solution we found effective was option D and using the business layer to try and keep things in sync (encasing in transactions etc.)

like image 144
kemiller2002 Avatar answered Sep 21 '22 14:09

kemiller2002