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.

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)

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
1  
Great, so I don't need to do anything then (FK constraints already there in the underlying tables). Thanks for the answer. –  Troy Sep 9 '11 at 20:33
1  
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. –  Derek Downey Sep 9 '11 at 20:34
    
@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. –  Jack Douglas Sep 12 '11 at 17:07
add comment

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.

share|improve this answer
add comment

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.