Foreign Key constraint in VDB views

teiiduser Oct 9, 2014 11:30 AM

Are foreign key constraints honored when doing an insert through the VDB, when the underlying source model does not have any such constraint?

Thanks!

1. Re: Foreign Key constraint in VDB views

shawkins Oct 9, 2014 11:35 AM (in response to teiiduser)

You'd have to do the insert via an instead of trigger with the appropriate check. It would be a valid enhancement request for Teiid to do this automatically (it has to do something similar to validate security constraints), but it would probably still need some additional metadata to make the check required and default to the current behavior.
Actions
2. Re: Foreign Key constraint in VDB views

teiiduser Oct 9, 2014 12:37 PM (in response to shawkins)

Thanks for your quick response?
Create View Parent
(
col1
)
Create View Child
(
colChild1

CONSTRAINT FK_col1
FOREIGN KEY (colCHild1)
REFERENCES Parent(col1),
)
This is what I have, but the source model does not have any such constraints, it would allow any values for colChild1. I have and Insert instead of trigger. Are you saying this constraint is not sufficient, teiid does not do this automatically and I would need to make the check myself?
Actions
3. Re: Foreign Key constraint in VDB views

shawkins Oct 9, 2014 1:31 PM (in response to teiiduser)

Yes, you would need to manually check referential integrity for the relevant insert/update/delete operations on the parent/child.
Actions