need help in primary key and foreign key
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,Relation开发者_StackOverflowToPatient)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...
- My second problem is: How to create the Identity column as
varchar
. For example: suppose I want to increment myGuardians
Table with 'GRD0001', 'GRD0002', 'GRD0003'
like this...
Thanks, S.D
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!
精彩评论