Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Pretoria, South Africa
    Posts
    3

    Unanswered: Master detail foreign key error

    I am busy with some development using C++ Builder and commitgreSQL as underlying db.

    I have two tables Quotes (master table) and Quote_lines (detail table). Each table has an id field of type integer and is set to autoincrement. Table Quote_lines also has an quote_id field of type integer which is a foreign key to the id field in the Quotes table.

    When I do an insert on the Quotes table, all is ok, but the problem comes in with the Quote_lines table. When I do an insert on the Quote_lines table and the Quotes table's record is not yet commited, it gives me an "ERROR: ExecInsert: Fail to add null value in not null attribute 'quote_id'".

    As far as I gather from the situation, the detail records are commited before the master record is commited and at that stage there is no value assigned to the id field of the master table and thus the commit fails.

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    foreign key violation

    I doubt this database system is postgresql

    commitgresql <> postgresql
    I did not hear anything about that thing

    and autoincrement sounds like mysql

    in postgresql this runs in one transcation and therefore
    this is NOT a problem
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Nov 2003
    Location
    Pretoria, South Africa
    Posts
    3
    Hi

    Sorry about this. I am not too clued up with the terms so I made a bit of a hash of it. I wrote the original post and then I realized, post is a term used in C++ Builder and commit should be better understood in the database world. I then did a search replace and replaced post with commit and unfortunately that's where comitgreSQL came from. I apologize.

    So the database is definitely PostgreSQL and about the autoincrement. That is again maybe the wrong description. There is a sequence defined for those fields which then should not be required when doing a insert on the table.

    Sorry for the inconvenience and thanks for the reply...

  4. #4
    Join Date
    Sep 2003
    Location
    Wisconsin, USA
    Posts
    34
    Well, this is basic relational database stuff.

    The details table -has- to have a quote_id reference to identify which quote these details are for. This is a Good Thing, because quote details without a quote don't make any sense.


    If you're using transactions, yes, you will have to make sure you issue a COMMIT on the Quote insert before adding anything to the Quote_Lines, and then use the new quote_id when inserting your quote_lines.

  5. #5
    Join Date
    Nov 2003
    Location
    Pretoria, South Africa
    Posts
    3
    Thanks guys for your input.

    I eventually managed to solve the problem after I read a message that was posted on the Borland website.

    The problem is with the components, because the details table is automatically posted first, before the quote_id field in the master table is available.

    Here is the link for the solution : http://community.borland.com/article...,25213,00.html

Posting Permissions

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