Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: How to refer a column when the referencing column is an identity column

    Hi all,

    The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

    i cannot implement this constraint, it throws the error when i execute the below Alter query,
    Code:
    ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) 
    REFERENCES parent_table (parent_id) ON DELETE CASCADE
    the error thrown is :
    Code:
    Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
     fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE 
    CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be 
    created where the referencing column 'child_table.child_id' is an identity column.
    any workarounds for this ?

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    it does not seem logical at all.
    you have some column that's Identity and still you need it to be a FK column for some other table!!!

    I don't see any chance for this to succeed.

    All you can do is implement some logic in the SPROC to ensure the values in the Identity column are unique and do away with the Identity characteristics of this column.

    Hope i've not confused you...
    In GOD we believe. Everything else we Test!

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Shefu
    The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
    I'm afraid that either the requirement is wrong or you have misunderstood it. The values of the fields in a foreign key are determind by the parent not the child. This should be an identity in the parent and then a matching datatype in the child's foreign key (e.g. smallint, int etc).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Quote Originally Posted by pootle flump
    I'm afraid that either the requirement is wrong or you have misunderstood it. The values of the fields in a foreign key are determind by the parent not the child. This should be an identity in the parent and then a matching datatype in the child's foreign key (e.g. smallint, int etc).
    Was thinking on the same line, but was not sure about this.

    Thanks for re-enforcing my personal thoughts.
    In GOD we believe. Everything else we Test!

Posting Permissions

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