Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Help About Subtype/Supertypes!

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by itiwcsingkaww View Post
    the truth here is that im using MS Access database .
    just curious ... why didn't you post in the MS Access forum?

    Quote Originally Posted by itiwcsingkaww View Post
    and i can't do this please anyone help me..
    you will have to show us your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    I don't know if that will make it easier to spot the problem or harder.
    chuckle

    nice explanation, by the way

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    71
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •