Sql-server – Surrogate keys as Foreign Keys

database-designsql serversurrogate-keyt-sql

I have a question about Surrogate keys, say i have two tables(in sql server):

Table A: Student_ID, Name, School, Section
Table B: Book_ID,Student_ID, IssueDate, BookName

If Student_ID is a clustered surrogate key and Name is unclustered index. How would one manually enter data into Table B without knowing the Student_ID. I know i might have to do a lookup in table A to find the IDs but that would not be efficient. Is there any way to make this efficient? I am trying to think how surrogate keys essentially work in terms of efficiency as a foreign key when considering inserts.

Best Answer

Assuming Student_ID is an identity

INSERT INTO [student] ([name]) VALUES ('penny');
declare @studentID int =  (select @@IDENTITY);
insert into studentBood (studentID, bookID) values (@studentID, 1);

insert into [studentBook] ([studentID], [bookID]) 
select [student].[iden], 13
from [dbo].[student] 
where name = 'penny';

select * from studentBook

select * from student
Related Question