How to solve "Self referencing table conflicted with the FOREIGN KEY constraint"

Question

I am creating a simple table with automatically generated employee id with a prefix like

sale_900000,sale_900001,sale_900002,sale_900003

It is a self-referencing table.

When I insert data into the table I get errors like this:

*Msg 547, Level 16, State 0, Procedure tr_generate_emp_id, Line 42
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "Registration_Registration". The conflict occurred in database "test", table "dbo.Registration", column 'empid'.*

For automatic employee id I am using an instead of insert trigger on the table

This is my table

CREATE TABLE [dbo].[Registration](
    [empid] [varchar](40) NOT NULL,
    [id] [int] IDENTITY(900000,1) NOT NULL,
    [First_Name] [varchar](40) NULL,
    [Last_Name] [varchar](40) NULL,
    [Address] [varchar](40) NULL,
    [E_Mail] [varchar](40) NULL,
    [Country] [varchar](40) NULL,
    [Mobile_No] [varchar](40) NULL,
    [Designation] [varchar](40) NULL,
    [managerID] [varchar](40) NULL,
 CONSTRAINT [PK_Registration] PRIMARY KEY CLUSTERED 
(
    [empid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Registration]  WITH CHECK ADD  CONSTRAINT [Registration_Registration] FOREIGN KEY([managerID])
REFERENCES [dbo].[Registration] ([empid])

and this is my trigger

ALTER TRIGGER [dbo].[tr_generate_emp_id] ON [dbo].[Registration]
    INSTEAD OF INSERT
AS
    BEGIN
        DECLARE @id INT
        DECLARE @id1 INT
        DECLARE @id_s VARCHAR(50)

        DECLARE @empid VARCHAR(50)
        DECLARE @First_Name VARCHAR(50)
        DECLARE @Last_Name VARCHAR(50)
        DECLARE @Address VARCHAR(50)
        DECLARE @E_Mail VARCHAR(50)
        DECLARE @Country VARCHAR(50)
        DECLARE @Mobile_No VARCHAR(50)
        DECLARE @Designation VARCHAR(50)
        DECLARE @managerID VARCHAR(50)

            SELECT  @id = Id
            FROM   dbo.Registration
             IF @id IS NULL
             SET @id = 899999

              SET @id1 = @id + 1
           SELECt  @First_Name = First_Name ,
                    @Last_Name = Last_Name ,
                    @Address = Address ,
                    @E_Mail = E_Mail ,
                    @Country = Country ,
                    @Mobile_No = Mobile_No ,
                    @Designation = Designation ,
                    @managerID = managerID 

            FROM    INSERTED

              SET @id_s = CONVERT(VARCHAR(50), @id1)
              SET @empid = 'Sale_' + @id_s

             INSERT INTO dbo.Registration
                      ( empid ,
                        First_Name ,
                        Last_Name ,
                        Address ,
                        E_Mail ,
                        Country ,
                        Mobile_No ,
                        Designation ,
                        managerID 

                      )
              VALUES  ( @empid , -- empid - varchar(40)
                        @First_Name , -- First_Name - varchar(40)
                        @Last_Name  , -- Last_Name - varchar(40)
                        @Address , -- Address - varchar(40)
                        @E_Mail , -- E_Mail - varchar(40)
                        @Country, -- Country - varchar(40)
                        @Mobile_No , -- Mobile_No - varchar(40)
                        @Designation  , -- Designation - varchar(40)
                        @managerID -- managerID - varchar(40)
                      )
    END

i am inserting

INSERT INTO dbo.Registration
        ( empid ,
          First_Name ,
          Last_Name ,
          Address ,
          E_Mail ,
          Country ,
          Mobile_No ,
          Designation ,
          managerID
        )
VALUES  ( '' , -- empid - varchar(40)
          'admin' , -- First_Name - varchar(40)
          'jon' , -- Last_Name - varchar(40)
          's-24' , -- Address - varchar(40)
          'abc@gmail.com' , -- E_Mail - varchar(40)
          'india' , -- Country - varchar(40)
          '098735322211' , -- Mobile_No - varchar(40)
          'manager' , -- Designation - varchar(40)
          ''  -- managerID - varchar(40)
        )

and error

Msg 547, Level 16, State 0, Procedure tr_generate_emp_id, Line 42
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "Registration_Registration". The conflict occurred in database "test", table "dbo.Registration", column 'empid'.
The statement has been terminated.

Your first issue is....triggers are set based, not row based. http://www.jimmcleod.net/blog/index.php/2008/06/05/triggers-set-based-not-row-based/ — granadaCoder, Mar 20 '13 at 15:31
I believe the issue is that you're creating a never ending trigger. You try to insert into a table which fires trigger that says instead of inserting into the table to do some work and then insert into the table, but I believe that insert will fire the trigger again starting the process over and resulting in an endless loop. I would instead do an update to the inserted records based upon some logic after you insert them. — Eric J. Price, Mar 20 '13 at 15:42
second issue is... you `SELECT @id` in the trigger might return any of the existing rows. There is no guarantee that this logic will give you an unused @id value. Use `MAX()` instead, or better, use the existing `IDENTITY` column in an after trigger. — Sebastian Meine, Mar 20 '13 at 15:44

score 1 · Answer 1 · answered Mar 20 '13 at 15:50

The main issue is that you're doing this in the first place. I just read through your trigger. Just changed empid to be a computed field...

CREATE TABLE [dbo].[Registration](
    [empid] AS ('Sale_' + Convert(Varchar(50),id)) PERSISTED,
    [id] [int] IDENTITY(900000,1) NOT NULL,
    [First_Name] [varchar](40) NULL,
    [Last_Name] [varchar](40) NULL,
    [Address] [varchar](40) NULL,
    [E_Mail] [varchar](40) NULL,
    [Country] [varchar](40) NULL,
    [Mobile_No] [varchar](40) NULL,
    [Designation] [varchar](40) NULL,
    [managerID] [varchar](40) NULL,
 CONSTRAINT [PK_Registration] PRIMARY KEY CLUSTERED 
(
    [empid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Way to look at the whole problem! – granadaCoder Mar 20 '13 at 15:59 — granadaCoder, Mar 20 '13 at 15:59

score 0 · Answer 2 · answered Mar 20 '13 at 15:52

0

In your trigger I think this line

SELECT  @id = Id

is meant to read

SELECT  @id = MAX(Id)

I'm surprised you havent received PK violation errors as a result. The FK violation error can only be due to trying to insert MangerId value that doesn't match an empid value (as far as I can see from the code). So check what managerId value you are supplying.

answered Mar 20 '13 at 15:52

Young Bob

733
3
9

@Love2Learn's answer is much better, anway triggers are something to avoid in general as discussed here: http://stackoverflow.com/questions/460316/are-database-triggers-evil – Young Bob Mar 20 '13 at 19:11

score 0 · Answer 3 · answered Mar 20 '13 at 15:52

Check your Nest Level.

http://msdn.microsoft.com/en-us/library/ms182737(v=sql.100).aspx

I would put this value in a (dbo.) AuditTable, something that has nothing to do with your primary table. trigger_nestlevel()

Change from Row Based to Set Based Logic.

score 0 · Answer 4 · answered Mar 20 '13 at 15:57

I re-did your trigger to be set based but I think @Love2Learn may have a better solution for you.

ALTER TRIGGER [dbo].[tr_generate_emp_id] ON [dbo].[Registration]
    INSTEAD OF INSERT
AS
    BEGIN
    DECLARE @id INT

        -- Get the last id used.
        SELECT  @id = MAX(Id)
        FROM   dbo.Registration

        IF @id IS NULL
            SET @id = 899999

         WITH MyInserted AS (SELECT
                        ROW_NUMBER() OVER(ORDER BY E_Mail) AS rownum-- Email is just a random order by here.  Could be anything you want.
                        ,*  
                        FROM inserted)
         INSERT INTO dbo.Registration
                  ( empid ,
                    First_Name ,
                    Last_Name ,
                    Address ,
                    E_Mail ,
                    Country ,
                    Mobile_No ,
                    Designation ,
                    managerID 

                  )
         SELECT -- add the last id used with the rownum (ranges from 1 to num of rows)
                'Sale_'+CONVERT(VARCHAR(50), @id+rownum) -- empid - varchar(40)
                First_Name, -- First_Name - varchar(40)
                Last_Name, -- Last_Name - varchar(40)
                Address,  -- Address - varchar(40)
                E_Mail, -- E_Mail - varchar(40)
                Country, -- Country - varchar(40)
                Mobile_No, -- Mobile_No - varchar(40)
                Designation, -- Designation - varchar(40)
                ManagerID  -- managerID - varchar(40)
        FROM MyInserted
END

How to solve "Self referencing table conflicted with the FOREIGN KEY constraint"

4 Answers4