none
How can I set the foreign key when the primary key of the other table is autonumeric? RRS feed

  • Question

  • I have two tables:

    TableA(IDA, value,...)

    TableB(IDB, IDA, value, ...)

    IDA and IDB are autonumeric. I am using SQL Server 2014 Express.

    I would like to create a new row on TableA and then a new row on TableB that is child of the new row on the TableA.

    So the code would be:

    Insert into TableA (value) values('123');
    Insert into TableB(IDA, value) values(???, '567');

    The IDA is given by the database, so how can I set the IDA foreign key in the TableB?

    Thank so much.

    Sunday, April 26, 2015 7:13 AM

Answers

  • >The IDA is given by the database, so how can I set the IDA foreign key in the TableB?

    If you're using Entity Framework then this is handled for you.  You just set the navigation property

    var b = db.TableB.New();

    b.TableA = a;

    db.SaveChanges();

    In TSQL you can use the SCOPE_IDENTITY() function to return the last value generated. EG

    Insert into TableA (value) values('123');
    declare @id int = scope_identity();
    Insert into TableB(IDA, value) values(@id, '567');

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, April 26, 2015 4:12 PM

All replies

  • >The IDA is given by the database, so how can I set the IDA foreign key in the TableB?

    If you're using Entity Framework then this is handled for you.  You just set the navigation property

    var b = db.TableB.New();

    b.TableA = a;

    db.SaveChanges();

    In TSQL you can use the SCOPE_IDENTITY() function to return the last value generated. EG

    Insert into TableA (value) values('123');
    declare @id int = scope_identity();
    Insert into TableB(IDA, value) values(@id, '567');

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, April 26, 2015 4:12 PM
  • I have a doubt with this solution.

    If a second process do another insert between the insert in the first process and the declare ID, which value I get, the ID given for the first process or the last ID that is for the second process?

    Thanks so much.

    Sunday, April 26, 2015 6:08 PM
  • scope_identity() will return the last INENTITY value generated in your session, and on the same code scope.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, April 26, 2015 7:51 PM