CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Pritish
Pritish

Reputation: 2224

Error FOREIGN KEY constraint while running Store Procedure while inserting NEWID() ID's run time

Created Tables

create table Empl(E_ID nvarchar(36) primary key, Name varchar(50), Designation varchar(50)) 

create table Location(L_ID nvarchar(36) primary key, Location varchar(50), E_ID nvarchar(36) foreign key references Empl(E_ID)) 

create table Contact_Emp (C_ID nvarchar(36) primary key, Contact_Number varchar(50), Lo_ID nvarchar(36) foreign key references Location(L_ID))

Store Procedure

alter proc InsertMultiplevalue  
(  
@Name varchar(50),  
@Designation varchar(50),  
@Location varchar(50),  
@Contact varchar(50)  
)  
as  
begin  
        insert into Empl values(newid(),@Name, @Designation)  
        declare @Employee_ID nvarchar(36) = NEWID()  
        insert into Location values(@Location,@Employee_ID)  
        declare @Cot_ID nvarchar(36) = NEWID()  
        insert into Contact_Emp values(@Contact,@Cot_ID)  
end

Ececute SP

exec InsertMultiplevalue 'UserName','Programmer','India','[email protected]'

I am getting this error

Msg 547, Level 16, State 0, Procedure InsertMultiplevalue, Line 25 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Location__E_ID__33D4B598". The conflict occurred in database "LocalDBTest", table "dbo.Empl", column 'E_ID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure InsertMultiplevalue, Line 27 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Contact_E__Lo_ID__36B12243". The conflict occurred in database "LocalDBTest", table "dbo.Location", column 'L_ID'.

Upvotes: 0

Views: 289

Answers (1)

Pritish
Pritish

Reputation: 2224

changes in STore procedure it works

alter proc InsertMultiplevalue  
(  
@Name varchar(50),  
@Designation varchar(50),  
@Location varchar(50),  
@Contact varchar(50)  
)  
as  
begin  
          declare @Employee_ID nvarchar(36) = NEWID()  
        insert into Empl values(@Employee_ID,@Name, @Designation)  

        declare @Location_ID nvarchar(36) = NEWID() 
        insert into Location values(@Location_ID,@Location,@Employee_ID)  

        declare @Cot_ID nvarchar(36) = NEWID()  
        insert into Contact_Emp values(@Cot_ID,@Contact,@Location_ID)  
end

Upvotes: 0

Related Questions