CODEWITHSUNDEEP
sqldatabaseoracle-database
user3158095
user3158095

Reputation: 15

Foreign key referring to more than one primary key values(from one table) - Oracle SQL PLUS

I am still a beginner in SQL and i'm facing an issue. hope you can help me. I have a table called Department where it has an attribute DEPARTMENT_NO as its primary key.

CREATE TABLE DEPARTMENT(
 DEPARTMENT_NO INT NOT NULL,
 NAME VARCHAR(25) NOT NULL,
 LOCATION CHAR(15),
 PRIMARY KEY(DEPARTMENT_NO));

I have another table called Doctor where it has an attribute DNUM as a foreign key referring to DEPARTMENT_NO :

CREATE TABLE DOCTOR(
 DOCTOR_ID CHAR(9) NOT NULL,
 DNUM INT NOT NULL,
 NAME VARCHAR(20) NOT NULL,
 DOB DATE,
 SPECIALTY VARCHAR(20) NOT NULL,
 SALARY INT,
 CITY VARCHAR(15),
 STREET VARCHAR(15),
 START_DATE DATE,
 PRIMARY KEY(DOCTOR_ID))
 FOREIGN KEY(DNUM) REFERENCES DEPARTMENT(DEPARTMENT_NO));

A doctor can be working in one or two departments. So, if I have a doctor working in department 1 and 4 (the values for DNUM will include 1 and 4).

I initially chose the data type of DNUM to be INT(same as DEPARTMENT_NO data type). But INT is not ideal for multiple values.

What should the data type be? or what other solution i have if,for example, I run a query for returning the name of the doctors working in department 4. The query should return all the names of doctors working in department 4(only) and the ones who work in multiple departments(including 4).

Thanks very much in advance and sorry for the long message.

Upvotes: 0

Views: 1515

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The standard way to represent a "many to many" relationship is via a "junction" (aka "link") table:

CREATE TABLE DOCTOR_DEPARTMENT (
    DOCTOR_ID INT REFERENCES DOCTOR(DOCTOR_ID),
    DEPARTMENT_NO INT REFERENCES DEPARTMENT (DEPARTMENT_NO),
    PRIMARY KEY (DOCTOR_ID, DEPARTMENT_NO)
);

Note the key on {DOCTOR_ID, DEPARTMENT_NO}, which ensures the same doctor cannot be connected to the same department twice.

It also implicitly creates a composite (aka. "concatenated") index on these fields in that order, which makes it very quick to find departments of a given doctor (via an index range scan). If you need to query in the opposite "direction" (for doctors of the given department), flip the order of fields. If you need both queries, then you'll need both indexes (i.e. you'll need to create one index explicitly).

Consider adding ORGANIZATION INDEX clause, if you need just one of these indexes.

Upvotes: 1

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

You can create another table with relation to these 2 tables Say, 

Create table Dept_Doctors(
ID int not null,
DOCTOR_ID char(9) not null,
DEPARTMENT_NO INT NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY(DEPARTMENT_NO) REFERENCES DEPARTMENT(DEPARTMENT_NO),
FOREIGN KEY(DOCTOR_ID) REFERENCES DOCTOR(DOCTOR_ID));

You can join the 3 tables and get the desired result.

Upvotes: 1

Joe Love
Joe Love

Reputation: 5932

You need an additional table called doctor_department

create table doctor_department
(doctor_id integer references doctor(doctor_id) not null,
 dnum integer references department(dnum) not null
)

Upvotes: 1

Related Questions