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)
 Cannot perform referencial integrity

Author  Topic 

lonifasiko
Starting Member

11 Posts

Posted - 2005-08-31 : 05:16:10
Hi all,

My problem is around 5 tables of my database:

1) Table "Patient" with patientId (GUID) as primary key.
2) Table "Doctor" with doctorId (GUID) as primary key.
3) Table "Nurse" with nurseId (GUID) as primary key.
4) Table "Relative" with relativeId (GUID) as primary key.

5) Table "UserRole" which contains:
- username (Primary key)
- password
- role (values: "Patient", "Doctor", "Nurse" or "Relative")
- personalId (must exist it's corresponding record in only one of the four tables listed above).

When I create a patient, I must also give him a username and pass to access the application. Each of the four entities will only have one record in UserRole table. Therefore, the relationship is 1 to 1.

The problem is I cannot make relationships between UserRole-Doctor, UseRole-Nurse, UserRole-Patient and UserRole-Relative. Why? Because if I try to insert a doctor's id in personalId field of UserRole, referencial integrity of UserRole-Nurse, UserRole-Patient and UserRole-Relative bumps because that value does not exist in neither Nurse nor Patient nor Relative tables.

This issue must have a very simple answer but I'me getting mad on this. I've tried with check constraints from SQL Server Enterprise Manager but gives me some errors when making expressions of this kind:

------------------------------------------------
personalId = (Select doctorId from Doctor) OR personalId = (select nurseId from Nurse) OR personalId = (select patientId from Patient) OR personalId = (select relativeId from Relative)
--------------------------------------------------

Can you seed me some ligth on this please? Thanks in advance.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-08-31 : 05:32:12
1) Try embedding the expression in a UDF, then use the UDF in a CHECK constraint
2) Create a View using the WITH CHECK OPTION

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-31 : 07:24:40
What if you create a UNIQUE constraint on personalId in UserRole table,
and reverse the FK constraints.

You could also remove the role column. If a personalId has a corresponding record in e.g. the Patient table, then that user has a Patient role.
If you don't use any further constraints, a user can then have multiple roles.
(Albeit with the same username & password)
Go to Top of Page

lonifasiko
Starting Member

11 Posts

Posted - 2005-08-31 : 07:30:15
Thanks for the reply.

I've created an UDF function (called checkPersonalId) that returns me 1 if the record exists and 0 if not. Function works weel because I've tried with Query Analyzer.

How do I add the check constraint to a column in SQL Server. What should be the expression? This way:

(select checkPersonaId(personalId)) = 1

Does not work. Any other ideas? SHould be very simple.



------------
MIGUELÓN
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-31 : 07:34:01
quote:
1) Try embedding the expression in a UDF, then use the UDF in a CHECK constraint
2) Create a View using the WITH CHECK OPTION
3) Consolidate the 4 tables into one, and add a column for PersonType to store "Doctor", "Patient", "Nurse" or "Relative". These tables are all storing the same type of information, they should be in one table.

If you have to support a legacy app that needs the original table names, create views with the same name that would return the same information:

CREATE VIEW Doctor AS SELECT PersonID as DoctorID, Name FROM Person WHERE PersonType='Doctor'
CREATE VIEW Patient AS SELECT PersonID as PatientID, Name FROM Person WHERE PersonType='Patient'

...etc.

The UserRole table will have a foreign key to the new Person table in any event.
Go to Top of Page

lonifasiko
Starting Member

11 Posts

Posted - 2005-09-01 : 03:20:40
Hi again guys,

byrmol: finally achieved calling the UDF from a check contraint clause this way: dbo.checkpersonalId(personalId) = 1
Thanks very much.

rockmoose: I have a unique contraint on personalId. What do you exactly mean by "reverse FK contraints". Could you explain a little bit more please? Role column is needed because to extract information related to a patient, I need to know what table I must query. And this information is only in Role column. I cannot query one table behind another till I fin the corresponding record in e.g. Patient table.

robvolk: I deeply thought about the idea of having one unique table but the information of a Patient is far different from the information stored for a Doctor and so on, therefore I finally decided to have separate tables.

Thanks very much to all. I would be glad in continuing receiving more new ideas or points of view from you. Regards.

------------
MIGUELÓN
Go to Top of Page

HCLollo
Starting Member

49 Posts

Posted - 2005-09-01 : 09:29:12
Hi lonifasiko!

Why don't you try to mix what you're already doing with what robvolk
suggested? Like storing all common info in one table, and adding
other tables like PatientExtraInfo, DoctorExtraInfo, etc.? So you
won't be bothered by unique ID thing and could keep track of things
like, for example, a doctor getting sick and becoming a patient

HCL


"If it works fine, then it doesn't have enough features"
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-01 : 11:54:01
Example;

This maintains RI and 1 role membership.
It is not possible to update the role in the UserRole table, and You can't insert a Doctor into Patient table.
Looks a bit messy I know , but it's failsafe.
You have to first insert the "UserRole" then the specifics in the correct Doctor/Patient/Relative table.

UserRole
(
username primary key
,password
,role check(role in('Patient', 'Doctor', 'Nurse' or 'Relative'))
,personalId unique
,common_stuff
,unique(personalId,role)
)

Doctor
(
doctorId primary key
,role check(role = 'Doctor')
,doctorinfo_stuff
,foreign key FK_UserRole_Doctor(doctorId,role) references UserRole(personalId,role)
)

Patient
(
patientId primary key
,role check(role = 'Patient')
,patientinfo_stuff
,foreign key FK_UserRole_Patient(patientId,role) references UserRole(personalId,role)
)
Go to Top of Page

lonifasiko
Starting Member

11 Posts

Posted - 2005-09-02 : 02:39:00
Thanks very much for your time and deep explanation (example included! ).

I have now understood your point of view. The unique thing I dislike from your approach is to have to insert first in the UserRole table instead fo the Patient/Doctor table. A patient's data could be inserted in the database but momentaneously without a username to access the application. Besides, patientId, doctorId...primary keys are GUID-s, and are generated by default with newid() function, so I have to generate them before inserting in UserRole.

Oh my God! I think I'm a bit dizzy! You mean using newid() for UserRole table and using that GUID for inserting in the other tables, without using newid() function on this tables, don't you?

I have now my fingers in a lot of pies but I promise you I'll try your solution in a few days and post my results here soon.

See you guys, thanks again and regards.

------------
MIGUELÓN
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-02 : 18:05:39
quote:
Originally posted by lonifasiko

Thanks very much for your time and deep explanation (example included! ).

I have now understood your point of view. The unique thing I dislike from your approach is to have to insert first in the UserRole table instead fo the Patient/Doctor table. A patient's data could be inserted in the database but momentaneously without a username to access the application. Besides, patientId, doctorId...primary keys are GUID-s, and are generated by default with newid() function, so I have to generate them before inserting in UserRole.

Oh my God! I think I'm a bit dizzy! You mean using newid() for UserRole table and using that GUID for inserting in the other tables, without using newid() function on this tables, don't you?

I have now my fingers in a lot of pies but I promise you I'll try your solution in a few days and post my results here soon.

See you guys, thanks again and regards.

------------
MIGUELÓN



You are correct.
Here is a rephrasing of the two approaches.

First approach (yours + elaborated on by other posters)
-------------------------------------------------------
UserRole is a subclass of : the "union type" {Doctor Union Patient Union Relative}
UserRole inherits the attributes of one of the superclasses in the "union type" (the one it belongs to).
The key is created in one of the superclasses Doctor, Patient, Relative and is FK into UserRole.

Second approach (my example)
----------------------------
Doctor, Patient, Relative are subclasses (specializations) of UserRole.
Doctor, Patient, Relative inherits all the attributes of the superclass UserRole.
The Key is created in the superclass UserRole and is FK into one of the specializations Doctor, Patient, Relative.


Just offered as food for thought and discussion...

rockmoose
Go to Top of Page
   

- Advertisement -