Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a view that looks like this:

CREATE view   reference.Test WITH SCHEMABINDING  as

    SELECT        reference.HighlevelTestId as TestId, Name, IsActive,
                  cast(1 as bit) as IsHighLevelTest 
    FROM          reference.HighlevelTest

    UNION ALL

    SELECT        LowLevelTestId  as TestId, Name, IsActive, 
                  cast(0 as bit) as IsHighLevelTest  
    FROM          reference.LowLevelTest

GO

NOTE: HighLevelTestId and LowLevelTestId are guaranteed to never have the same values (no conflicts).

I have another table that looks like this:

CREATE TABLE [Reference].[TestAddition](
    [TestId] [BigInt] NOT NULL,
    [OtherStuff] [bit] NOT NULL,
    .... 
) 

I would really like to FK my TestAddition table to my Test view on the TestId column (for referential integrity and ease of use with OData).

Is there any way to do that? (Any way to change my view to make this work?)

share|improve this question
    
I think the best would be to have another table (say reference.AllLevelTest with only one column TestId which will have all the TestIds, from both tables (modifying Insert/Delete procedures to the two tables, to enforce integrity.) Then, you can make that new table the target of FKs. –  ypercube Jul 25 '13 at 15:06
1  
Or combine the two tables in one (why two tables which seem to have identical structure?) –  ypercube Jul 25 '13 at 15:12
1  
Right, why not have a single table with a column to indicate high or low? Anyway, you can't foreign key views - even indexed views. –  Aaron Bertrand Jul 25 '13 at 15:30
    
@ypercube - They don't have the same structure. Just a few overlapping columns shown here. –  Vaccano Jul 25 '13 at 15:33

1 Answer 1

The short answer: This is not possible.

The long answer: Problems like this usually arise from a non-optimal database design. In your case you have two types of tests. They are different, but both represent tests. Therefore you should have one table dbo.tests that contains the common attributes (columns) of all tests.

Each test type has specific attributes. that are not shared with other test types. So for each test type you should have an additional table that contains the primary key of dbo.tests with a declared FK relationship, followed by the test type specific attributes.

With that structure you can easily declare a FK relationship between tests and other tables. If the relationship needs to include all test types, the FK points to the dbo.test table. If the relationship needs to include only a specific test type, the FK points to the specific table for that test type.

To make the transition to the new table layout easier, you could create a view for each test type that contains the common columns and the specific columns. Those views then look similar to the tables you currently have. On those views you can create triggers to handle insert, updates and deletes directing the data between the common and the specific tables for each type. That way you could use the view the same way you are using your current tables.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.