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)