Foreign-Key Reference on View

Question

Is there any possibility to reference to a field of a view to be sure about integrity?

Pseudocode example:

CREATE VIEW V_ONE AS SELECT .....
CREATE TABLE TWO ( ID INT REFERENCES V_ONE ( field ))

I don't want to CREATE TABLE T_ONE instead of V_ONE first to be able to reference T_ONE.

@edit:

View V_ONE collects data from multiple tables of an erp-system an preprocesses them. Table TWO extends V_ONE with some additional data, which should not be included into V_ONE. There must not be an entry in table TWO for every entry in V_ONE, but I want to be sure there's no entry in table TWO for which no base-data can be found in V_ONE.

A view is just a query, there is no data there to validate, unless you're talking about the underlying table(s). Can you explain why the constraint can't be against whatever table is behind the view? — Aaron Bertrand, Jan 14 '13 at 13:16
Please post the select for the view and the name of the field you want to reference. — paparazzo, Jan 14 '13 at 14:48
@AaronBertrand The view generates an `ID` per row which is used for all further processing of its data and it would be nice to check against this `ID`. The view itself is treated as a kind of data-interface between ERP-tables and ERP-AddOn. — Nico, Jan 14 '13 at 20:59

Raphaël Althaus · Answer 1 · 2013-01-14T13:49:03.183

5

Short answer : no. Sql Server doesn't allow to use a Foreign Key on a view.

You could put the Foreign Key constraint on "one of the table" referenced in the view, but without context... Hard to give a convenient answer.

Edit : well, what about a check constraint on Two, with a query on v_one ?

But you'll have to find a way to manage deletion (and updates) on "source tables" of v_one.

You could use (untested) an INSTEAD OF DELETE (and INSTEAD OF UPDATE if needed) trigger on your view (V_ONE) with the check constraint on your table (TWO).

edited Jan 14 '13 at 13:49

answered Jan 14 '13 at 12:58

Raphaël Althaus

59,727
6
96
122

longer answer: http://stackoverflow.com/questions/1928355/tsql-foreign-keys-on-views – mcalex Jan 14 '13 at 13:03
@mcalex: I know about the linked question. But it does not exactly cover my question. He asked for asuring data integrity via inserting data into the view. I instead want to reference from a table to a view! – Nico Jan 14 '13 at 13:06
what about using a trigger(s) to implement the constraint ? – tschmit007 Jan 14 '13 at 13:15
@tschmit007 Thought about the same. Instead of insert trigger on Table TWO which checks for existing PK in V_ONE. – Nico Jan 14 '13 at 13:22
@RaphaëlAlthaus Trigger on ERP tables won't work. Ever worked with DynamicsAX? ;) – Nico Jan 14 '13 at 13:34
@Nico Didn't even ever hear the name ;) Well, you could maybe add a trigger on your view (// with check constraint), but I never tried it : see http://www.java2s.com/Tutorial/SQLServer/0440__Trigger/CREATEINSTEADOFDELETETRIGGERONaview.htm (see also edited post) – Raphaël Althaus Jan 14 '13 at 13:39

score 0 · Answer 2 · answered Jan 14 '13 at 13:22

You can achieve a similar result with a check constraint rather than a Foreign key:

-- CREATE VIEW THAT RETURNS DUMMY DATA
CREATE VIEW V_ONE
AS
SELECT  Field
FROM    (VALUES (1), (2), (3)) T (Field);
GO

-- CREATE FUNCTION THAT VALIDATES CRITERIA
CREATE FUNCTION dbo.CheckFunction (@ID INT)
RETURNS BIT AS
BEGIN

    IF EXISTS (SELECT 1 FROM V_ONE WHERE Field = @ID)
    BEGIN
        RETURN 1;
    END

    RETURN 0;

END
GO
-- CREATE TABLE THAT REFERENCES VIEW
CREATE TABLE T_TWO (ID INT NOT NULL CONSTRAINT CHK_T_TWO_ID CHECK (dbo.CheckFunction(ID) = 1));

-- TRY INSERTING A VALUE NOT RETURNED BY V_ONE
INSERT T_TWO VALUES (4);

You will probably need to implement triggers to handle deletes/updates to the source of V_One, You should probably add a foreign key to the source too.

Foreign-Key Reference on View

2 Answers2