#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11

    NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented


    I am trying to create a table (simple enough) with one foreign key, and it gives me this message:

    NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented

    Does that mean that the constraint is not yet implemented because there's no data in the table, or that this construct is not yet implemented in my currently-used version of the database software?
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    What exactly was the CREATE TABLE syntax you used?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    CREATE TABLE jeremys_table (jeremy_id INTEGER,
    some_fk INTEGER,
    PRIMARY KEY (jeremy_id),
    FOREIGN KEY (some_fk) REFERENCES mediabarcodes(bar_code));




    simple enough
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by metaBarf
    simple enough
    No, its not simple enough. For starters, you don't provide the table definition to the other table you are trying to reference. That's the key to determining if your table creation statement should work.

    Also, somehow I have the notion that you are not using PostgreSQL, because "NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented" looks suspiciously like a MySQL error message. In fact, most versions of MySQL before 4.0 do exactly that: they allow the foreign key syntax, but ignore it in the actual table creation.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    here is the output from pg_dump for the referenced table. I was told by the admin that we are running version 6.5.1

    it would be pretty tough to do your job if you don't know what kind of limitations are in the system you're using. This is a dated version of postgresql, and from what I can tell it is without support for lots of modern-time goodies, most notably and related the inability to add a foreign key to an already-existing table. It was a simple table and your cut at it was not helpful.



    $ pg_dump -s -t mediabarcodes wpd_<>
    CREATE TABLE "mediabarcodes" (
    "bar_code" character(20) NOT NULL,
    "employee_id" int4,
    "creation_date" timestamp,
    "media_size" int4,
    "media_type" int4,
    "media_orientation" character,
    "sheets_per_media_cassette" int4,
    "target_printer" int4,
    "allocated" bool,
    "on_cassette_in_shipping_carton" bool,
    "valid_encoding" character);
    CREATE UNIQUE INDEX "pk_media_bar_codes_id" on "mediabarcodes" using btree ( "bar_code" "bpchar_ops" );
    CREATE INDEX "barcodes_index" on "mediabarcodes" using btree ( "bar_code" "bpchar_ops" );
    CREATE INDEX "types_sizes_sheets" on "mediabarcodes" using btree ( "media_type" "int4_ops", "media_size" "int4_ops", "sheets_per_media_cassette" "int4_ops" );
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by metaBarf
    here is the output from pg_dump for the referenced table. I was told by the admin that we are running version 6.5.1
    Wow... That is quite an old version of PostgreSQL. Why didn't you explain this from the start? Now the error message makes sense.


    it would be pretty tough to do your job if you don't know what kind of limitations are in the system you're using. This is a dated version of postgresql, and from what I can tell it is without support for lots of modern-time goodies, most notably and related the inability to add a foreign key to an already-existing table. It was a simple table and your cut at it was not helpful.
    I was not making a cut at your simple table, but at your remark from the pervious post where you said "Simple enough". That seemed to be kind of dismissive of anyone's efforts to help.

    If you expect people here to take the time to answer your questions, you should at least take the time to be descriptive about your problem, and the circumstances around it. (The How to Post Questions page in the PHP forum comes to mind here).

    P.S. I will mention that even with a modern version of PostgreSQL, your foreign key constraint would not work, because jeremys_table.some_fk is an integer column, while mediabarcodes.bar_code is a CHAR(20). Constrained foreign keys need to be of the same type (or domain, for modern PostgreSQL).

    Other than this, PostgreSQL 6.5.1 supports foreign key constraints, and you would not be adding the constraint to the existing table, but creating the constraint with a new table, so I would think it should work if you have the right types on the related columns. But again, having no experience with PostgreSQL that old, I can't guarantee it. If it still doesn't work, you might want to search the mail archives at http://archives.postgresql.org , or even ask a question in the mail lists.
    Last edited by rycamor; July 21st, 2003 at 12:14 PM.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    This is a dated version of postgresql, and from what I can tell it is without support for lots of modern-time goodies, most notably and related the inability to add a foreign key to an already-existing table
    6.5.1 is horrendously old, the copyright on the docs at postgresql.org say 1999. Is there no way you can update the version? I don't know when fk support was added, but it's quite possible that 6.5.1 didn't support it yet. Besides the fk stuff you're missing there are also numerous additions, fixes, and optimizations that your database will benefit from by upgrading. If it's possible, get the admin to upgrade...

    -b
    PostgreSQL, it's what's for dinner...
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    I guess bcyde started posting before I finished . But anyway, foreign key support is available in version 6.5, as per the documentation (scroll down for version 6.5.). Please read it, if you haven't yet. Even then, PostgreSQL was quite an advanced DBMS, but it was lacking in the area of performance and scaleability.

    I wholeheartedly agree with bcyde that your sysadmin need to upgrade PostgreSQL as soon as possible. Aside from the great advances in performance and capability, I believe there were a few (just a few) security vulnerabilities over the past few years.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Doh... yup rycamor, right again , didn't check the docs close enough, and didn't have any personal experience with PG that old.

    -b
    PostgreSQL, it's what's for dinner...
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    thank you both for your replies and consideration. I've been trying to get them to upgrade, you've probably provided me with more concise reasons that I can now give than what I had, which was that stuff I was trying to do didn't work!!

    I didn't mean to be nondescriptive of the problem, I did not take enough time to figure out all the angles of the problem when I originally posted.

    I apologize for seeming aggravated about some aspects of this, most of my frustration with the project does in fact stem from the fact that I cannot convince anyone around here to upgrade this archaic version of postgresql... We have problems stemming from the absolute necessity of validating any new/changed software ( something about FDA approval). I wanted it to be simple and straightforward as all my previous experience has been with Oracle 9i, but it doesn't always work out the way you want. It basically comes down to me wanting to upgrade and do things a certain way (like use the built-in procedural languages and other fun stuff that utilizes relational design and the SQL standard), and being limited to the way that I'm being told to do it
  20. #11
  21. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Hey metaBarf, no hard feelings ,

    Here are a few other reasons why PostgreSQl version 7.3 (even better -- 7.4 when it comes out) are worth the upgrade:

    1. Domains -- After learning a little more about relational data theory, I now consider domains to be an essential part of a serious RDBMS. They provide a powerful way to control the datatypes in your columns. The upcoming version 7.4 will provide for CHECK constraints in domains.

    2. Schemas -- essentially the same thing as namespaces. Any one database can be divided into different schemas, and tables inside any one schema can even be named the same as tables in another schema in the same database, but having different ownership/permissions. Queries can be written across schemas.

    3. Set-returning functions -- Make a complex function behave exactly like a table to the "outside world". A function can now return columns and rows as if is a table. SELECT column1, column2 FROM my_function('any_parameter') WHERE [search_clause] ...etc. And there are many other improvements to the stored-procedures and rule system.

    4. Significant performance and stability improvements. For example, you no longer need to take your database off-line to VACUUM ANALYSE.

    5. Tons of great 3rd party add-on software and libraries -- just see http://gborg.postgresql.org/.

    Heck, don't take my word for it: just read the release notes to each of the major releases since 6.5:

    7.0
    7.1
    7.2
    7.3
    7.4 Development branch

    It's getting very good. With version 7.5, I believe we can expect to see some sort of standard replication/clustering to be included (for now, you can get replication as a commercial add-on by buying a support contract at www.pgsql.com).

    Note also, that the later versions of PostgreSQL tend to behave a lot like Oracle 8i/9i
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo