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

1

Good! I am learning sql about a week ago and I have some problems understanding how foreign keys work, specifically creating inserts in an application where the foreign key is receiving null value. (and each table id is receiving its respective value and data). for example

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

 client(id, name, contact, SSN)

The process table 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 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)";                       

the insert works fine when I check the data in the process table, idclient is null. and this 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 given as null in idclient and how could it be solved so that the value would be assigned automatically.

Thank you very much for your help! Rest of a good weekend!

    
asked by anonymous 01.12.2018 / 22:44

2 answers

0

Hi, Marco, if I understood correctly, you wanted the FK of the 'process' table to receive the 'customerID' from the 'client' table.

Well, if this is it, you would not have two options:

1) Perform the insert command of the 'client' table followed by the insert of the 'process' table, if and only if you get the client id, established by you as identity in the creation of the table and passing as an inclusion parameter to the insert of the 'process' table. In this case, you need, between one 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 that can better clarify how to get the identity value to use in the insert in the 'client' table.

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

I hope I have helped.

    
02.12.2018 / 01:41
0

Marco, after adding the client you must obtain the value that was generated for the idclient column. 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's a 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;
    
02.12.2018 / 01:45