foreign key

[Maha Sh.]

Hi all,

I created a database for my web application in VB and I'm trying to insert data in my login table but its keep giving me error

i have login table which contains
loginID
UserId
Acctype
Password

My PK is LoginID

MyFK is User Id which connected to three tables(Admin-Pateint-SLP) the problem I think is that some times the UserID is the same So i could have admin where his Id is 1 and a patient who is also have the same user Id. I think the solution is to do something about the acctype because I have three typs of users(A-P-S) but i dont know how to do it?

Plz help!!

[|-|x]

What is the error you are getting?

I don't believe you can have an actual foreign key set up on the table pointing to multiple other tables, but it shouldn't be causing an error with duplicate values unless you have specified that field as unique

Copy your table/database structures and the data you are trying to insert here and we may be able to help identify the source of the problem.

[Maha Sh.]

Hello Hearth, Thanks for your replay :)

I just changed the structures of the table but the problem I am not sure if it the right way or not?

i made a search about the FK and the PK but still confused!!

i have three type of users each open have diffrent pages?

So in the begging my tables were like this
login table ( loginID, UserID, Acctype,Password) the LoginID will be PK

the User ID will be a FK and it will connected to 3 tables which are

SLP table the PK is SLP ID
Patient table the PK is PatientID
Admin Table the PK is AdminID

i have others table that depend on the Patient ID as the clinicalReport table

so i change it to
the logint table (userID, Password, acctype) the PK is the User ID

so the other tables will be connected with login table in the UserID
SLP table the FK UserID
Patient table the FK UserID
Admin table the FK UserID

could you tell me plz if its wright or wrong i didn't enter the data yet.

[Maha Sh.]

No replay :'(

i deleted all the changes that i made in my tables

the error that accure when I'm trying to insert data in my table is

The insert Statment conflicted with the foreign key the conflict occurred in the databse

i figureout the error now

my login table which contain
login ID as PK
UserType
Password
UserId

i connected the userID to three other tables like i mentioned before

so when I'm insert the userId in the Logintable its check with (SLP_ID, AdminID,PateintID)

so i insert
User Type:SLP password:111 UserID:3

so its takesthe user ID and its goes to the admin table to find a match but its not exist in it

i have the user type in the table so what i want to do is to check the user type then goes to its table and check the id.

I am thinking to use check constrains option I am not sure if its will help or not!!!

or may be i shoud create a new table called Usertype and connect the logintable and the other tables to it ?!!!

[|-|x]

I think you have understood the problem correctly. The database will not work correctly if the UserID is Foreign Key to 3 separate user tables.

Rather than trying to condition the FK based on the Type field, it might be better in this scenario to split the UserID into 3 separate fields, each FK linked to the appropriate user table.

Example

CREATE TABLE login (
  LoginID varchar(20) not null,
  AdminID int null,
  PatientID int null,
  SLP_ID int null,
  Password varchar(20) not null,
  PRIMARY KEY (LoginID),
  KEY AdminID (AdminID),
  KEY PatientID (PatientID),
  KEY SLP_ID (SLP_ID)
);
ALTER TABLE login
  ADD CONSTRAINT login_fk1 FOREIGN KEY (AdminID) REFERENCES Admin (AdminID),
  ADD CONSTRAINT login_fk2 FOREIGN KEY (PatientID) REFERENCES Patient (PatientID),
  ADD CONSTRAINT login_fk3 FOREIGN KEY (SLP_ID) REFERENCES SLP (SLP_ID);

If you can post the SQL create scripts for your tables, and a sample data set we will be able to get a better idea of how to help you.

[Maha Sh.]

Thanks Hearth :)
Your solution was much better and simpler
i done what you said and its working now

thanks alot :)