Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Location
    norway
    Posts
    5

    Unanswered: Referencing a foreign key inside an object/type

    I have an ordinary table Employee with personID as primary key, and an object PersonalProgram wich is derived from its parent Trainingprogram.

    I need personID from Employee as a foreign key in PersonalProgram. I know I can make Employee an object and refer to the object inside PersonalProgram, but this is not an option. Employee must be an ordinary table.

    Does anybody know how this is done? and the syntax for it?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Referencing a foreign key inside an object/type

    Originally posted by piten231077
    I have an ordinary table Employee with personID as primary key, and an object PersonalProgram wich is derived from its parent Trainingprogram.

    I need personID from Employee as a foreign key in PersonalProgram. I know I can make Employee an object and refer to the object inside PersonalProgram, but this is not an option. Employee must be an ordinary table.

    Does anybody know how this is done? and the syntax for it?
    I have read and re-read your question, but I can't make out quite what you are trying to do here. What do you mean by saying you have an "object" called PersonalProgram?

    If you mean a TYPE, then it is meaningless to create a foreign key from a TYPE to a TABLE - it's like creating a foreign key from VARCHAR2 to a table, doesn't make sense!

    If you mean a table based on a TYPE then you can create a foreign key:

    SQL> create table employee (personid number primary key);

    Table created.

    SQL> create type personal_program_t as object
    2 ( personid number
    3 , programid number
    4 );
    5 /

    Type created.

    SQL> create table personal_program of personal_program_t;

    Table created.

    SQL> alter table personal_program add foreign key (personid) references employee;

    Table altered.

    Similarly, if you have a normal table with a column based on a TYPE, you can still create a foreign key:

    SQL> create table personal_program2( id number primary key, data personal_program_t );

    Table created.

    SQL> alter table personal_program2 add foreign key (data.personid) references employee;

    Table altered.

    I hope this helps.

  3. #3
    Join Date
    Apr 2003
    Location
    norway
    Posts
    5

    not quite...

    I mean type when I say "object" called PersonalProgram :-).

    The thing is that I have a 1-to-many relation between type PersonalProgram and table Employee. PersonalProgram is a child of TrainingProgram and I have a table of TrainingProgram-types.
    How can I represent this kind of relationship?

    Heres the sql for it:

    CREATE TABLE Employees_tab(
    personID integer,
    jobDescription varchar2(20),
    FOREIGN KEY (PersonID) REFERENCES Persons_tab(PersonID),
    PRIMARY KEY (PersonID));

    CREATE OR REPLACE TYPE TrainingProgram_ty AS OBJECT(
    programID integer,
    name varchar2(20),
    description varchar2(30),
    programType varchar2(20),
    programLevel integer)
    NOT FINAL NOT INSTANTIABLE;

    CREATE OR REPLACE TYPE Personal_ty UNDER TrainingProgram_ty();

    CREATE TABLE TrainingProgram_tab OF TrainingProgram_ty (UNIQUE (programID), programID NOT NULL);

    Thanks for all help :-)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: not quite...

    Originally posted by piten231077
    I mean type when I say "object" called PersonalProgram :-).

    The thing is that I have a 1-to-many relation between type PersonalProgram and table Employee. PersonalProgram is a child of TrainingProgram and I have a table of TrainingProgram-types.
    How can I represent this kind of relationship?

    Heres the sql for it:

    CREATE TABLE Employees_tab(
    personID integer,
    jobDescription varchar2(20),
    FOREIGN KEY (PersonID) REFERENCES Persons_tab(PersonID),
    PRIMARY KEY (PersonID));

    CREATE OR REPLACE TYPE TrainingProgram_ty AS OBJECT(
    programID integer,
    name varchar2(20),
    description varchar2(30),
    programType varchar2(20),
    programLevel integer)
    NOT FINAL NOT INSTANTIABLE;

    CREATE OR REPLACE TYPE Personal_ty UNDER TrainingProgram_ty();

    CREATE TABLE TrainingProgram_tab OF TrainingProgram_ty (UNIQUE (programID), programID NOT NULL);

    Thanks for all help :-)
    So you want a foreign key from table TrainingProgram_tab to table Employees_tab, is that right?

    In that case, you need a column (or attribute) of INTEGER type in TrainingProgram_tab corresponding to Employees_tab.PersonID. So you are either going to have to change the table or change the TYPE it is based on - look at the 2 examples I wrote earlier.

    Can I ask, are you doing this as a learning exercise, or is there a real business application for this? Because I can see no reason for not using conventional tables in this case, other than as an experiment.

  5. #5
    Join Date
    Apr 2003
    Location
    norway
    Posts
    5
    No, this is not a real-case. It's a school-assignment wich I'll get grades based on. Ofcourse I wouldn't have done it this way if it was in real life.

    I want a foreign key Employee_tab.PersonID in TYPE Personal_ty or something equivalent. Personal_ty is a child of TrainingProgram_ty. The case is that there are several types of trainingprograms. Only personal trainingprograms (Personal_ty) should point to an Employee. Therefor it would be wrong having a column (or attribute) of INTEGER in TrainingProgram_tab corresponding to Employees_tab.PersonID, because then all kinds of TrainingPrograms would reference an employee...

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by piten231077
    No, this is not a real-case. It's a school-assignment wich I'll get grades based on. Ofcourse I wouldn't have done it this way if it was in real life.
    I am so relieved

    Originally posted by piten231077
    I want a foreign key Employee_tab.PersonID in TYPE Personal_ty or something equivalent. Personal_ty is a child of TrainingProgram_ty. The case is that there are several types of trainingprograms. Only personal trainingprograms (Personal_ty) should point to an Employee. Therefor it would be wrong having a column (or attribute) of INTEGER in TrainingProgram_tab corresponding to Employees_tab.PersonID, because then all kinds of TrainingPrograms would reference an employee...
    So you want an additonal attribute PersonID in the Personal_ty type:

    CREATE OR REPLACE TYPE Personal_ty UNDER TrainingProgram_ty
    (PersonID INTEGER);

    But the foreign key constraint will have to be on the table. Now I don't have access to O9i right now, so I can't play with this, but would hope the syntax would be:

    alter table Training_Program_tab add foreign key (personid) references Employees_tab;

    Not sure though, since table is based on the supertype and personid belongs to the subtype...

  7. #7
    Join Date
    Apr 2003
    Location
    norway
    Posts
    5
    Thanks for you help! This was basically what I came up with as well...just wasn't sure...maybe there would be a better way to do this...but I guess not. Just gotta say that objects in Databases sucks as far as I'm concerned!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by piten231077
    Thanks for you help! This was basically what I came up with as well...just wasn't sure...maybe there would be a better way to do this...but I guess not. Just gotta say that objects in Databases sucks as far as I'm concerned!
    Yup. However, there is a handy feature called Object Views that allows you to create normal, relational tables then create views on them that convert the data to an OO form. That way you can satisfy the OO-crazy developers without corrupting the database!

  9. #9
    Join Date
    Apr 2004
    Posts
    1
    Hi!

    I've tried something similar, but because the table is based on the supertype and the foreign key is based on a column in the subtype I get the error message:
    ORA-00904: invalid column name

    My case is that I have a type for person, and a subtype for handler. This handler is related to a type called department.

    Any ideas?

Posting Permissions

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