Results 1 to 3 of 3

Thread: SQL syntax prob

  1. #1
    Join Date
    Jun 2004
    Posts
    127

    Unanswered: SQL syntax prob

    Hi im having trouble using alter statements when attempting to implement a FK relationship.Here is my SQL Code as follows:

    CREATE TYPE modelElement_typ AS OBJECT (
    ID INT,
    namespace_ID INT);

    CREATE TYPE namespace_typ AS OBJECT (
    ID INT);

    CREATE TABLE namespace_tbl OF namespace_typ (
    PRIMARY KEY (ID));

    CREATE TABLE modelElement_tbl OF modelElement_typ (
    PRIMARY KEY (ID),
    UNIQUE (namespace_ID),
    namespace_ID REFERENCES namespace_tbl (ID) ON DELETE CASCADE);

    Im having difficulty tring to implement a FK from namespace_tbl (ID) to the modelElement_tbl (ID) column, ive tried a whole bunch of ways, but as im using types, i think this is making things difficult. has anyone any pointers to offer?

    kind regards,

    andrew.

  2. #2
    Join Date
    Dec 2004
    Posts
    7
    I execute your comands, they processed without errors, and referential integrity work
    Code:
    SQL> descr modelElement_tbl ;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER(38)
     NAMESPACE_ID                                       NUMBER(38)
    
    SQL> insert into modelElement_tbl values (1,11);
    insert into modelElement_tbl values (1,11)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SCOTT.SYS_C003806) violated - parent key not found
    
    SQL> insert into namespace_tbl values (11);
    
    1 row created.
    
    SQL> insert into modelElement_tbl values (1,11);
    
    1 row created.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you mean this, Andrew?

    ALTER TABLE namespace_tbl
    ADD CONSTRAINT fk_ne FOREIGN KEY (id)
    REFERENCES modelElement_tbl (id);

Posting Permissions

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