title: How to solve "Self referencing table conflicted with the FOREIGN KEY constraint"
body: <p>I am creating a simple table with automatically generated <code>employee id</code> with a prefix like </p>
  
  <pre><code>sale_900000,sale_900001,sale_900002,sale_900003 
  </code></pre>
  
  <p>It is a self-referencing table.</p>
  
  <p>When I insert data into the table I get errors like this:</p>
  
  <blockquote>
    <p>*Msg 547, Level 16, State 0, Procedure tr_generate_emp_id, Line 42<br>
    The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "Registration_Registration". The conflict occurred in database "test", table "dbo.Registration", column 'empid'.*</p>
  </blockquote>
  
  <p>For automatic employee id I am using an <code>instead of insert</code> trigger on the table  </p>
  
  <p>This is my table </p>
  
  <pre><code>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])
  </code></pre>
  
  <p>and this is my trigger </p>
  
  <pre><code>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
  </code></pre>
  
  <p>i am inserting </p>
  
  <pre><code>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)
          )
  </code></pre>
  
  <p>and error </p>
  
  <pre><code>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.
  </code></pre>