How to insert a value in the Foreign Key column?

advertisements

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.


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.