Cross table constraint -- possible?

Consider these four tables:
TableA:
Columns: ID (PK), Type (FK-TableA_Types), etc.
TableA_Types:
Columns: ID (PK), Name
Table B:
Columns: ID (PK), Type (FK-TableB_Types), etc.
TableB_Types:
Columns: ID (PK), Name

TableA has a FK column to the TableA_Types table (TableA.Type->TableA_Types.ID), and TableB has a FK column to the TableB_Types table (TableB.Type->TableB_Types.ID).

The problem is that TableA_Types and TableB_Types overlap, and should really be in the same table. The problem is, they're not, and the given the large amount of work that it would involve in consolidating the tables, it would preferable to simply add a constraint to TableA_Types and TableB_Types such that their IDs don't overlap.

In other words, I want to avoid this situation:
TableA_Types:
1 name1
2 name2

TableB_Types:
1 name3
2 name4

Can I add a constraint on TableA_Types such that it prevents new records from having the same IDs as any ID for records in the TableB_Types table, and vice versa?

If so, how? If not, any other suggestions?

Thanks!Are these identity fields? If you can get away with changing the identities for these tables, just re create the identitiy columns so that one table only uses odd numbers and another table only uses even numbers.|||

This is not possible with a constraint.

You could achieve this with a trigger if you really want this situation to be blocked.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

To directly answer you questions -No, not a table constraint; and yes, you have alternatives.

You can use the UNIQUEIDENTIFIER datatype for the primary key in each table -there will never be any overlap. But I don't think that that really makes much sense in this situation. The keys are too large and unwieldy.

Cam's suggestion is interesting, and just 'might' work for you. Set the IDENTITY seed = 1 in one table, and the seed = 2 in the other table, and the increment value = 2 in both tables. As long as the row counts in each table don't approach 1 billion, you 'should' be OK. Set a table constraint in each table that maintains the odd or even scheme.

And Wesley's suggestion to use a TRIGGER is the most 'bullet proof' method -all the while requiring the most server resources.

But the real issue is this: Why do you not take the time and effort and correct a underlaying defect that you know exists in the database?

Leaving it is like having dryrot in your house and just painting over it.

Once I know that dryrot has been painted over, I'm instantly on alert for other signifianct defects waiting like time bombs ...

|||You can "sort of" do it. A constraint can actually access other tables through a function, but it can get messy to do this. So like several other folks have said, a trigger is the thing to do|||

I agree with Arnie though, correcting the database design is actually the best solution.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thanks for the posts, guys. I've implemented a check using triggers. Not the most elegant or best solution, I agree, but it will have to do for now.