0

So I've got three different address tables, one for countries with states (USA, Canada, Australia), one for New Zealand were there is an ID system (NZ Post API), and another for everywhere else. (don't suggest to me that I should be using one table, I know this sounds like a ridiculous setup but I have a good reason for doing it this way)

I have used a sequence to give a key to each of these tables. Every record has key that is unique not only to it's own table but across all three.

My problem is this: I now want to reference these keys from another table, but I want the reference to look at all three tables.

Is there any easy way to do this? If not I can use a UDF but I'm looking to keep things simple.

It's really important that this database is both solid and easy to understand as many graduate level programmers will be writing code against it.

Thomas Horrobin
  • 400
  • 2
  • 13
  • create a view that is a union of your three tables. If not just create a single address table with common fields and keep different fields in the three tables (which would be a "correct" design). – koriander Jan 11 '15 at 10:58
  • Thanks, I will try using views. Regarding your second suggestion, if I was to make a 1 to 1 relationship to the three tables from a main address table, how would I enforce that relationship on the main table? – Thomas Horrobin Jan 11 '15 at 11:06
  • there are no simple way to do what you want. views doesn't support foreign key constraints – Andrey Morozov Jan 11 '15 at 13:17
  • For the second option, see http://stackoverflow.com/questions/10292355/how-do-i-create-a-real-one-to-one-relationship-in-sql-server – koriander Jan 11 '15 at 15:26

0 Answers0