Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: Creating tables with inherited types in Oracle 9i

    Hey guys, I'm a newbie here but may well use these forums in future since I'm going to be making a database-driven online game soon. Meanwhile I have a problem with some database coursework I have to do using Oracle 9i. The point of the exercise is to learn about object relational databases. I get the basic ideas behind using types and storing objects of those types in tables (instead of the traditional approach of just creating the tables) but it's giving me grief. This is my problem:

    I have two types - 'person_t' and 'student_t' - with one inheriting from the other:

    CREATE TYPE person_t AS OBJECT (
    p_name VARCHAR2(50),
    p_address address_t,
    dob DATE,
    tel_number NUMBER(15)
    ) NOT FINAL;
    /

    CREATE TYPE student_t UNDER person_t (
    student_id NUMBER(10),
    term_addr address_t,
    year NUMBER(1),
    prev_mark NUMBER(3),
    is_contained_in REF course_t
    ) FINAL;
    /

    These type definitions are fine - they're implemented and work fine. I can create a table of 'person_t', thus:

    CREATE TABLE person_tab OF person_t (
    p_name PRIMARY KEY
    );

    And if I create a 'student_t' object, using that table definition, and perform a query to retrieve it, I can obtain ALL the attributes including the specialist 'student_t' ones. The problem comes when I want to set constraints on my table definition. As you saw I know how to set the PRIMARY KEY constraint. And I can set others such as UNIQUE and NOT NULL for the generic 'person_t' attributes. However, how do I set constraints on the 'student_t' attributes? You can't simply say:

    CREATE TABLE person_tab OF person_t (
    p_name PRIMARY KEY,
    p_address NOT NULL,
    dob NOT NULL,
    student_id UNIQUE,
    term_addr NOT NULL,
    year NOT NULL
    ) ;

    because the compiler doesn't recognise the attributes 'student_id', 'term_addr', and 'year' as 'person_t' attributes. Any help will be gratefully recieved. Thanks for reading this far

  2. #2
    Join Date
    Nov 2003
    Posts
    3
    For starters: bump.

    I don't like doing that but I can't help feeling someone must know the answer to my question since most of the topics here seem a lot more advanced. Please - if anyone has a clue what I'm talking about please help. Thank you.

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

    Re: Creating tables with inherited types in Oracle 9i

    I struggle with this stuff because I never use it myself. I think the only people who ever use it are students on Oracle courses! All pain and no gain as far as I can see...

    I think the answer is more like:

    CREATE TABLE person_tab OF person_t (
    constraint person_pk PRIMARY KEY(p_name),
    constraint address_nn check (p_address IS NOT NULL),
    ... other constraints here
    );

  4. #4
    Join Date
    Nov 2003
    Posts
    3

    Re: Creating tables with inherited types in Oracle 9i

    Cheers for replying Tony. I quite agree - from what I can see there aren't many people implementing the O-R features of Oracle and I don't really see the point of them to be totally honest. What's wrong with just using a foreign key? Anyway, thanks for your suggestion but it's not quite what I needed. I'm going to have words with my lecturer tomorrow when I hand the work in 'cos I don't think he's really taught us what we needed to know for this work. He doesn't even seem to understand O-R databases himself. Always the way.

Posting Permissions

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