In SQL Server, I would write something in the line of this:
Code:
CREATE TABLE EMPLOYEE(
id BIGINT identity (1, 1) NOT NULL,
Type CHAR(1) NOT NULL
CONSTRAINT cc_EMPLOYEE_Type CHECK(Type in ('H', 'C')),
...
)
CREATE TABLE HOURLY(
EmployeeId BIGINT NOT NULL,
...
)
CREATE TABLE CONSULTANT(
EmployeeId BIGINT NOT NULL,
...
)
ALTER TABLE HOURLY
ADD CONSTRAINT FK_HOURLY_is_an_EMPLOYEE FOREIGN KEY (EmployeeId)
REFERENCES EMPLOYEE (Id)
ALTER TABLE CONSULTANT
ADD CONSTRAINT FK_CONSULTANT_is_an_EMPLOYEE FOREIGN KEY (EmployeeId)
REFERENCES EMPLOYEE (Id)
DECLARE @Id BIGINT
INSERT INTO EMPLOYEE (Type, ....)
VALUES('H', ...)
SELECT @Id = SCOPE_IDENTITY()
INSERT INTO HOURLY (EmployeeId, ...)
VALUES (@Id, ...)
If one would define two FK's between Employee and Hourly, things won't work. They call it the "crossing FK" thingy. If you notice that thingy, you know your data model is flawed. You could check that.
But I think you swapped the two tables in the FK definitions.
Wrong:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT FK_CONSULTANT_is_an_EMPLOYEE FOREIGN KEY (Id)
REFERENCES CONSULTANT(EmployeeId)
instead of
Right:
ALTER TABLE CONSULTANT
ADD CONSTRAINT FK_CONSULTANT_is_an_EMPLOYEE FOREIGN KEY (EmployeeId)
REFERENCES EMPLOYEE (Id)
You probably created the relation using the Access GUI. I don't know if that will make it easier to spot the problem or harder.
dBforums
>
Help About Subtype/Supertypes!
10-26-11, 09:32
Linear Mode
