Is it possible to create foreign key with where clause on the reference?
Something like this:
ALTER TABLE webapp.SimCards
ADD CONSTRAINT FK_SimTypes foreign key (type) references webapp.settings_types (id)
where TypeGroup = "SIM Type" ;
The issue is that we have many groups on the Settings_Types table and when using it has reference on the foreign key, it can match against other groups like products, devices, etc..
In order to guaranty consistence, I want to make sure that column "type" on table SimCards only validate if value exist on column id from settings_types but only on TypesGroups = "SIM Type"
If this is not possible, is there a better way than create a check constrain using a function to check result ?