SQL Server foreign key is giving null value when entering data in the main table

Question

Good! I’ve been learning sql for about a week now and I’m having some problems understanding how Foreign Keys work, specifically by creating Inserts in an application where Foreign key(foreign key) is receiving null value. (and each principal of the table is to receive its respective value and data). for example

 process:(id, idclient, processnumber, processcompany, Supervisorname, bank ,state)

 client(id, name, contact, SSN)

the tabla process has the following data and constraints:

    id int identity(1,1) not null,  
    idclient int,
    processnumber int not null, 
    processcompany nvarchar(150),
    supervisor nvarchar(150),
    bank nvarchar(150),
    state int not null, 
    constraint PK_Processo primary key (id, processnumber),
    constraint FK_ProcessClient foreign key(idclient)
    references client(id)         
    on update cascade
    on delete set null

);        

and these are the following most important data insertion queries for creating a new process:

        string insertclientquery = "insert into dbo.client(name, contact) values (@name, @contact)";
        string insertprocquery = "insert into dbo.process(processnumber, processcompany, bank, state) values (@processnumber, @processcompany, @bank, @state)";                       

Insert works well when I check the data in the process table, the idclient is null. and that is not what I intended, as in the following case:

   client(id=1, bla bla bla),
   process(id=1, idclient = null, bla bla bla)  

i would like to know why the value is null in the idclient and how it could be solved so that the value is automatically assigned.

I really appreciate your help! Good weekend rest!

Answer 1

Hi, Marco, if I understand correctly, you wanted FK from the 'process' table to receive the 'idClient' from the 'client' table'.

Well, if it is, wouldn’t you have two options:

1) Perform the Insert command of the 'client' table followed by the 'process' table Insert, if and only if you obtained the client id, established by you as Identity in the table creation and passing as inclusion parameter for the 'process' table Insert. In this case, you need, between an INSERT and another, to get the value of the Identity of the previous inclusion. For this case, I recommend reading the MSDN on SCOPE_IDENTITY which can better clarify how to get the value of Identity to use in the 'client' table'.

2) The other option is that you already have the client code, previously registered in another opportunity, and pass the id of the 'client' table as parameter to the 'process' table’s Insert'.

I hope I’ve helped.

Answer 2

Milestone, after the inclusion of the customer you must obtain the value that was generated for the column idclient. This is possible using the SCOPE_IDENTITY function().

It seems to me that both client and process inclusion should be part of a single transaction.

Here is the model in T-SQL; adapt it to the language used.

-- código #1
declare @idclient int;
BEGIN TRANSACTION
INSERT into dbo.client (name, contact) values (@name, @contact);
set @idclient= scope_identity();

INSERT into dbo.process (idclient, processnumber, processcompany, bank, state) values (@idclient, @processnumber, @processcompany, @bank, @state);
COMMIT;