Does a view need its own foreign key constraints?

Question

Disclaimer: I'm a programmer, not a DBA, so bear with me...

I have a view that I use to just map 2 entities together. I have to do a join between a few different tables to get that:

CREATE OR REPLACE VIEW V_SCREENING_GROUP_SITES AS (
SELECT SG.SCREENING_GROUP_ID, V.SITE_ID
FROM SCREENING_GROUP SG, VISIT V, VISIT_DATE VD
WHERE VD.VISIT_ID = V.VISIT_ID 
AND V.SCREENING_GROUP_ID = SG.SCREENING_GROUP_ID);

Above is just for context, don't worry too much about that. What I need to know is how to make the fields in my new V_SCREENING_GROUP_SITES view (SCREENING_GROUP_ID and SITE_ID) behave as foreign keys to the SCREENING_GROUP and SITE tables. Or does it even matter?

If it was a table I would do:

ALTER TABLE V_SCREENING_GROUP_SITES
ADD CONSTRAINT FK_SCREENING_GROUP_ID
FOREIGN KEY (SCREENING_GROUP_ID)
REFERENCES SCREENING_GROUP.SCREENING_GROUP_ID;
...

But since it's a view that obviously doesn't work. I couldn't find an ALTER VIEW syntax that works for setting FKs. What should I do?

(This is a MySQL Database)

Answer 1

A View is a logical table that is based on one or more physical tables. If there are foreign key relationships in the underlying tables, then they will be manifested in the view. Views are entirely dependent on the tables they are derived from, so trying to add foreign keys to them is not possible.

• Troy: Great, so I don't need to do anything then (FK constraints already there in the underlying tables). Thanks for the answer.
• Derek Downey: I think this is the point to make. You shouldn't need FKs on the view as long as the underlying tables have the FKs.
• Jack Douglas: @DTest - It could be very useful to be able to apply constraints, including check, unique and foreign key constraints to a view (especially if the view aggregates data). It just happens that no current RDBMSs enforce constraints on views even if they allow you to create them.
• Stijn de Witt: @JackDouglas Exactly right! In fact I came here trying to find out whether MySQL support such a feature.

Answer 2

In the strict sense of the word, no you cannot set foreign keys on views. Here is why:

InnoDB is the only built-in storage engine for MySQL that features foreign keys. Any InnoDB table will be registered in information_schema.tables with engine = 'InnoDB'.

Views, while registered in information_schema.tables, has a NULL storage engine. There are no mechanisms in MySQL to have foreign keys on any table that has an undefined storage engine.