CODEWITHSUNDEEP
sqlsql-server-2008insertforeign-keys
Mangrio
Mangrio

Reputation: 1030

How to insert value in Foreign Key column?

How Foreign Key column will be filled in the following scenario.

it is returning NULL value in tblCustomer's OrderId column, whereas it is set to foreign constraint. 

create table tblOrder
(
OrderId int primary key identity(1,1),
OrderName varchar(20) not null
)
create table tblCustomer
(
CustomerId int primary key identity(1,1),
CustomerName varchar(20) not null,
OrderId int FOREIGN KEY REFERENCES tblOrder(OrderId)
)

insert into tblOrder(OrderName) Values ('OrderOne')
insert into tblCustomer(CustomerName) values ('CustomerOne')

How it will get the foreign key result to store in column.

Upvotes: 1

Views: 7560

Answers (3)

eli_gazza
eli_gazza

Reputation: 1

Add this before INSERT TO :

SET FOREIGN_KEY_CHECKS=0;

And then insert the values you wanted

Upvotes: 0

sunysen
sunysen

Reputation: 2361

insert into tblCustomer(CustomerName, OrderId) 
values ('CustomerOne',(select OrderId from tblOrder where OrderName = 'OrderOne')

Upvotes: 1

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

SCOPE_IDENTITY() is what you want.

DECLARE @OrderId int;
insert into tblOrder(OrderName) Values ('OrderOne');

SET @OrderId = SCOPE_IDENTITY();
insert into tblCustomer(OrderId, CustomerName) values (@OrderId ,'CustomerOne');

But I think you've got your Primary/Foreign Key relationship the wrong way round. It would normally make more sense for the Foreign Key to be in tblOrder. The way you have it at the moment, an order can belong to many customers, but a customer can only make one order.

Upvotes: 2

Related Questions