need help in primary key and foreign key

Question

Hi all,

I need help in auto populating the primary key values in foreign key table while inserting data in foreign key table. For Example: I have created table:

create table Patient
(
PatientId int IDENTITY(1,1) primary key,
FirstName varchar(50),
SurName varchar(50),
Gender char(20),
)

Say 5 rows are there in this Patient Table: Say First Row value is: 1, Priya, Kumari, Female

I have created the Guardians Table:

create table Guardians
(
GuardiansId int identity(1,1) primary key,
PatientId int foreign key references Patient(PatientId),
FirstName varchar(50),
SurName varchar(50),
Gender char(20),
RelationToPatient varchar(50),
)

In this table Insert operations are like this:

insert into Guardians(FirstName, SurName, Gender,RelationToPatient)values('Sumit','Kumar','Male','Wife')

While selecting the Guardians Table PatientId showing NULL values: My query is while inserting the values in Guardians Table PatientId should be auto Populated which will come from Patient Table...

1. My second problem is: How to create the Identity column as varchar. For example: suppose I want to increment my Guardians Table with 'GRD0001', 'GRD0002', 'GRD0003' like this...

Thanks, S.D

Answer 1

Your question is not very clear - what exactly do you want to do??

When you insert something into the Guardians table, you want to automatically also insert it into the Patients table? I don't quite follow. Can you make a complete example, maybe??

If you need to capture the insert IDENTITY value from the Patient table, do this:

DECLARE @NewPatientID INT

INSERT INTO dbo.Patient(fields) VALUES(.......)

SET @NewPatientID = SCOPE_IDENTITY()

INSERT INTO dbo.Guardians(PatientId, ......) VALUES(@NewPatientID, ......)

As for your second question: leave you GuardiansId IDENTITY as it is (only an INT column can be an IDENTITY and you want to keep that - trust me!) and add a computed column to your table:

ALTER TABLE dbo.Guardians
  ADD GuardianIDWithPrefix AS 
    'GDR' + RIGHT('0000' + CAST(GuardiansId AS VARCHAR(4)), 4) PERSISTED

Since it's a PERSISTED field, you can even index on it and use it like a normal field in every respect.

That should do the trick!