i'm having trouble inserting record to a supertype/subtype relationship.
lets say i have my supertype EMPLOYEE and subtypes HOURLY and CONSULTANT. now i how can i insert a record to this kind of situation..
the truth here is that im using MS Access database . i mapped the supertype and subtypes and it results to one-to-one relationship and i enforced referential constraint.. when i tried to insert in the hourly employee table and added details to the Employee Table .. it says that "cannot insert record because related record is required in CONSULTANT" ..
What i really want to happen is that the primary key of my SUBTYPES corresponds to the Supertype EMPLOYEE ..
and i can't do this please anyone help me..
im a student and doesn't know much about DBMS ... please help me.
This article Implementing Table Inheritance in SQL Server - SQLTeam.comseems to explain one of the simplest ways to use a constraint to check whether a parents type is the correct type for the child table it also has data in. But I am stuck thinking of a way to implement the constraint of having to have a child existing. In the example in the article, a person could exist in the people table and be of type teacher, but not have an entry in the teachers table.
I thought that I could use triggers for this constraint, but I really don't know how to write something like that. Perhaps if there was a method of a trigger running after an entire batch rather than a single interaction on a table, I could write a solution. But I don't think such a method exists. Here is some code to show my requirements a bit more clearly