Single attribute used as a foriegn key for multiple tables

user_9753667 0
Viewed 32

I want to make Emp_id as a foreign key for Employee table and Record table. Can this be done?

This is my code

CREATE TABLE Empolyee(
EID varchar(8) NOT NULL,
E_name varchar(30) NOT NULL,
NID varchar(30) NOT NULL,
sex varchar(40) NOT NULL,
history varchar(30) NOT NULL,
salary varchar(10) NOT NULL,
cid int NOT NULL,
FOREIGN KEY (cid) REFERENCES Employee_Contact_No(CID),
FOREIGN KEY (cid) REFERENCES Employee_email(Email_ID),
PRIMARY KEY (EID)
);

Receptionist table

CREATE TABLE Receptionist(
r_id varchar(8) NOT NULL,
Emp_ID varchar(8) NOT NULL,
PRIMARY KEY (r_id),
FOREIGN KEY (Emp_ID) REFERENCES Employee(EID),
FOREIGN KEY (Emp_ID) REFERENCES Record(EID)
);

And the receptionist's records

CREATE TABLE Record(
record_no varchar(8) NOT NULL,
EID VARCHAR(8) Not Null,
patient_id varchar(15) NOT NULL,
discription varchar(30) NOT NULL,
appointment varchar(40) NOT NULL,
PRIMARY KEY (record_no)
);
mysql database
1 Answers

You can make a single field reference multiple tables, but I cannot think of a scenario where it would ever be a good idea; it would require both tables to have the same id value, and (at least imply) that those id values be meaningfully coordinated.

From the looks of your examples, it is probably more likely that you need tables like Employee_Contact_No and Employee_email to reference the Employee table.

user_41042240
Related