Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 composite primary key definition

Author  Topic 

insar
Starting Member

1 Post

Posted - 2004-06-09 : 07:58:52
[left]we have in our database a table for terror casualties data.
we fill in this table with data we get from hospitals in case there was a terrorist attack in town. When a casualty has not provided identification number, the hospital automatically retrieves the casualty file number as identification number.

one of the aims of our db application is to identify all casualties.
we are now designing the table for acsualties and we hesitate with the primary key:

create table casualties(casualtiesId varchar(9) not null,/*fileno or identification number!!!*/
constraint CasualtyEventCode int not null
constraint casualtyEventCode_fk foreign key
references person (IdNum),
constraint IdEventCode_pk primary key(casualtiesId, casualtiesEventCode),
CasualtyIdStatus binary,
casualtyHealthStatus nvarchar(30),
casualtyAdmissionDate DateTime,
casualtyReleaseDate datetime,
casualtyHospitalCode int
constraint casualtyHospitalCode_fk foreign key
references hospital(HospitalCode),
casualtyIntermediaryID varchar(9),
casualtyNote nvarchar(100));

create table person(FirstName nVarChar(25),
LastName nVarChar(25),
IdNum varchar(9),
ChildrenNo smallint,
Gender smallint,
FamilyStat int,
Profession nVarChar(25),
BirthDate DateTime,
ImmigrationDate DateTime,
Preligion int,
Citizenship nVarChar(20),
constraint IdNum_pk primary key(IdNum),
constraint Gender_ck check ((Gender>0) and (Gender<4)),
constraint FamilyStat_ck check ((FamilyStat>=0) and (FamilyStat<5)),
constraint FamilyStat_fk foreign key(FamilyStat)
references FamilyStatus(FamilyStatusNum),
constraint Preligion_ck check ((Preligion>=0) and (Preligion<6)),
constraint Preligion_fk foreign key(Preligion)
references Religion(ReligionNum));

in the meantime we decided to define a composite primary key for casualties. please note that the casualty's identification number is a foreign key from table person. The problem is that in case the casualty was given hospital file number instead of real identifiation number it is NOT A FOREIGN KEY.

How can we come out of this maze???

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-09 : 19:24:24

It's a sad state of affairs when you need a database for this type of thing.

Go to Top of Page

CtrlAltDel
Starting Member

17 Posts

Posted - 2004-06-10 : 07:41:04
Would this be a good place to use a surrogate key? (e.g. an identity field?) Since there doesn't appear to be a clear-cut choice? Additionally, if you used something like the person's id number, what happens if they get caught in multiple attacks? You wouldn't then be able to have their id as the primary key in the casualties table, as primary keys can't contain duplicates. Or perhaps the casualty file number - does each casualty get a unique file number?

quote:
It's a sad state of affairs when you need a database for this type of thing.


Too right, and my above reasoning would really suck when you have to consider the poor person who gets caught in multiple terrorist attacks...

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer
Go to Top of Page
   

- Advertisement -