Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: foreign key conflict - why?

    Hi,

    Getting the following error:

    INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_templates_types'. The conflict occurred in database 'coolit', table 'types', column 'id'.

    ...when I run this SP:
    Code:
    CREATE PROCEDURE template_addnew
    @tmpXnet int,
    @tmpName varchar(100),
    @tmpPDF varchar(150),
    @tmpThumb varchar(150),
    @tmpThumbLge varchar(150),
    @tmpType int,
    @tmpBleed varchar(10),
    @newID int OUTPUT
    AS
    INSERT INTO templates (extranet_id, template_name, pdf_path, thumbnail_path, thumbnail_lge_path, type_id, bleed) VALUES (@tmpXnet, @tmpName, @tmpPDF, @tmpThumb, @tmpThumbLge, @tmpType, @tmpBleed)
    SELECT @newID=@@IDENTITY
    GO
    The table structure is... uncomplicated. The "templates" table has a "type_id" field (and some other stuff); the "types" table has an "id" and a "type_name" field.

    The value I'm trying to enter into "templates" for the "type_id" is a valid id of a record in the "types" table.

    I'm not sure why I'm getting that error. I could delete the relationship but it strikes me as one I'd want to keep.. Can anyone help?

    Cheers

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    what happens when you use the following sp?

    Code:
    CREATE PROCEDURE template_addnew
    @tmpXnet     int,
    @tmpName     varchar(100),
    @tmpPDF      varchar(150),
    @tmpThumb    varchar(150),
    @tmpThumbLge varchar(150),
    @tmpType     int,
    @tmpBleed    varchar(10),
    @newID       int OUTPUT
    AS
    if not exists(select * From types where id = @tmpType) begin
       raiserror('Unable to find @tmpType (%d) in "types" tables.',16,1,@tmpType) with nowait
       return -1
    end
    
    INSERT INTO templates (extranet_id, template_name, pdf_path, thumbnail_path, thumbnail_lge_path, type_id, bleed) 
    VALUES (@tmpXnet, @tmpName, @tmpPDF, @tmpThumb, @tmpThumbLge, @tmpType, @tmpBleed)
    SELECT @newID=@@IDENTITY
    
    return 0
    GO
    Post the ddl for both tables, you might have an error in the FK
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Hi

    Sorry, my mistake. My ASP code was defining the parameters in a different order to that in which they appear in the SP. So it was trying to put a "type_id" number in the "templates" table that didn't, in fact, appear in the "types" table.

    Thanks for your help though.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Yup, so the 2 cent comment to your statment..." I could delete the relationship but it strikes me as one I'd want to keep.." I would agree!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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