Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Unanswered: Creating a database schema with cyclic foreign keys

    Hi, I am trying to create a schema with cyclic foreign keys like


    CREATE TABLE T1 (
    id INTEGER PRIMARY KEY,
    fkto2 INTEGER REFERENCES T2
    ....
    );

    CREATE TABLE T2 (
    id INTEGER PRIMARY KEY,
    fkto1 INTEGER REFERENCES T1
    ....
    );


    If I try to create these tables, db2 complains about T2 being an undefined name. Of course it does. Coming from Informix, I know a solution to this problem: Put all create-table-statements within a create schema-statement. I tried this, but I would get weired error messages. Seems the parser got confused by finding to many create table-statements within the create -schema-statement which apparently may not be seperated by a semicolon.


    Let me guess: the answer is: create the FOREIGN KEY -constraints AFTER creating all tables, right?

    Tell me that there is another solution, cause this would make my already complex schema even less readable.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's so wrong about adding an FK constraint after the table has been created? this happens with indexes, no?

    and what about a composite key, you have declare it "after" you declare the columns, not while you are creating them...


    rudy

  3. #3
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14
    There is nothing wrong about it, it is just akward:

    CREATE TABLE t1 (
    ...

    someforeignkeytotable45 INTEGER, --no REFERENCES here
    ...

    );

    ...
    ...
    ...


    CREATE TABLE t45 (

    ...

    );

    ALTER TABLE t1 ADD FOREIGN KEY (someforeignkeytotable45) reference t45

    The problem is, that the schema itself becomes really unreadable. and thus unmaintainable. The foreign keys that belongs to a table can be scattered all around the schema, which is highly error prone.
    Indices are not the same thing. You can create them directly after creating the table, they stand close to each other. So it stays readable.

    I hope I made my point clear.
    Do you have any idea why the use of CREATE SCHEMA didn't work for me? The CREATE TABLE statements I had embedded inside where all correct.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > The problem is, that the schema itself becomes really unreadable.
    > and thus unmaintainable. The foreign keys that belongs to a table
    > can be scattered all around the schema, which is highly error prone.

    that is a matter of opinion, not fact

    i prefer to look at diagrams, not read DDL

    if you are managing your database using DDL, then yes, i suppose this would be error prone

    sorry, i do know know what CREATE SCHEMA does


    rudy

  5. #5
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Question

    Yes, I am currently using DDL.
    What sort of diagrams do you refer to? Entity-relationship-diagrams?
    What tool are you using to create DDL from diagrams?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am using none at the moment, i have no current requirement to manage a large schema, and as a freelancer, if i get a contract which requires it, i will use the client's diagramming software, and if they don't have any, i might look at cheap or freeware options

    in the past i have used ERwin which is top-of-the-line

    if you are interested in this type of tool, see
    http://www.databaseanswers.com/modelling_tools.htm

    a minimum requirement for me would be that the tool be able to "reverse engineer" (import) the DDL to create a diagram, and from the diagram, be able to generate (export) the DDL


    rudy

  7. #7
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14
    Thank you for that link. I think using a tool would help a lot, but I am doing my diploma thesis in a "database centric" institute. Using such a tool would probably considered to be somewhat unacademic... ;-)
    The problem is not the conceptual conversion between ERM and a relational schema, I am quite versant with that. It is just the sheer size of the schema, which makes my DDL script rather unreadable. I have not even started creating triggers and assertions for some of the integrity constraints that can reasonably be enforced within the DB.

  8. #8
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Its always a good pracice to create tables first and create constraints in the end. If you ever see the output of db2look , it will create tables first and the foreign keys and it is very important to follow that order when there is parent child relationship !!!!

  9. #9
    Join Date
    Jul 2002
    Posts
    3
    What version of DB2 are you using?

    Cyclic FK creation within a create schema statement should work in v8.1, and it may work in slightly earlier versions, however I haven't tried this.

  10. #10
    Join Date
    Apr 2003
    Posts
    191
    Hi koganti,

    db2look used to be a very unwieldy tool to recreate related database objects. In my experience, which ranges from 5.1 to 7.2, the sequence of the generated statements always has been deficient.

    It was suitable, however, to recreate single objects or to document some sort of state of the database structure. It is just some sort of state, because if you diff the output of the same database some time after you created and dropped new objects, there are differences in the sequence of statements and you may be induced to believe the database actually has changed when it has not.

    Is this different now with version 8?

    Johann

    Originally posted by koganti
    Its always a good pracice to create tables first and create constraints in the end. If you ever see the output of db2look , it will create tables first and the foreign keys and it is very important to follow that order when there is parent child relationship !!!!

Posting Permissions

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