CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Fallenreaper
Fallenreaper

Reputation: 10682

inserting into A errors because of a foreign key contraint issue

Can someone help explain this to me and resolve it?

http://sqlfiddle.com/#!6/2adc7/9

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblMobileForms_tblForms". The conflict occurred in database "db_6_2adc7", table "dbo.tblForms", column 'fm_id'.: insert into tblMobileForms(fm_name) values ('lol')

My schema has the ID from tblMobileForms be a foreign key to tblForms.fm_id

Upvotes: 3

Views: 119

Answers (3)

GarethD
GarethD

Reputation: 69749

You could use an INSTEAD OF trigger to apply a random ID to each mobile form as it is inserted:

CREATE TRIGGER dbo.tblMobileForms_Insert
    ON dbo.tblMobileForms
    INSTEAD OF INSERT

AS
BEGIN
    DECLARE @Inserted TABLE (fm_ID INT, fm_html_file VARBINARY(MAX), fm_name NVARCHAR(50));
    INSERT @Inserted (fm_ID, fm_html_File, fm_Name)
    SELECT  fm_ID, fm_html_File, fm_Name
    FROM    inserted;

    IF EXISTS (SELECT 1 FROM @Inserted WHERE fm_ID IS NULL)
        BEGIN
            WITH NewRows AS
            (   SELECT  fm_ID, fm_html_File, fm_Name, RowNumber = ROW_NUMBER() OVER (ORDER BY fm_name)
                FROM    @Inserted
                WHERE   fm_ID IS NULL
            ), AvailableIDs AS
            (   SELECT  fm_ID, RowNumber = ROW_NUMBER() OVER (ORDER BY fm_ID)
                FROM    tblForms f
                WHERE   NOT EXISTS
                        (   SELECT  1
                            FROM    tblMobileForms m
                            WHERE   f.Fm_ID = m.fm_ID
                        ) 
                AND     NOT EXISTS
                        (   SELECT  1
                            FROM    inserted i
                            WHERE   f.fm_ID = i.fm_ID
                        ) 
            )
            UPDATE  NewRows
            SET     fm_ID = a.fm_ID
            FROM    NewRows n
                    INNER JOIN AvailableIDs a
                        ON a.RowNumber = n.RowNumber

            IF EXISTS (SELECT 1 FROM @Inserted WHERE fm_ID IS NULL)
                BEGIN 
                    RAISERROR ('Not enough free Form IDs to allocate an ID to the inserted rows', 16, 1);
                    RETURN;
                END
        END

        INSERT dbo.tblMobileForms (fm_ID, fm_html_File, fm_Name)
        SELECT  fm_ID, fm_html_file, fm_name
        FROM    @Inserted

END

When each row is inserted the trigger will check for the next available ID in tblForms and apply it sequentially to the inserted rows where fm_id is not specified. If there are no free ID's in tblForms then the trigger will throw an error so a 1 to 1 relationship is maintained (The error would be thrown anyway since tblMobileForms.fm_id is also a PK).

N.b. this requires tblForms.fm_ID to just be an int column, and not identity.

Upvotes: 1

Brad Ingram
Brad Ingram

Reputation: 191

To do what you are trying to do you cannot set up the FK on tblMobileForms as an identity. See my fiddle below for more information.

http://sqlfiddle.com/#!6/be6f7/2

Alternatively what you could do is to have tblMobileForms have it's own separate surrogate key and have a different FK column to the tblForms table.

Upvotes: 3

SchmitzIT
SchmitzIT

Reputation: 9552

The PK on the tblMobileForms table has the same name as the FK on the same table. Seeing the PK is an IDENTITY column, you can end up with non-matching values.

In my fiddle, the tblForms table contained IDs in the upper 60s. Running the INSERT in the child table would add a record with id 1, which does not exist in the parent table.

I'd create a new row in the tblMobileForms table, and reference that to the parent table.

Upvotes: 1

Related Questions