title: Foreign key referring to more than one primary key values(from one table) - Oracle SQL PLUS
body: <p>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.</p>
  
  <pre><code>CREATE TABLE DEPARTMENT(
   DEPARTMENT_NO INT NOT NULL,
   NAME VARCHAR(25) NOT NULL,
   LOCATION CHAR(15),
   PRIMARY KEY(DEPARTMENT_NO));
  </code></pre>
  
  <p>I have another table called Doctor where it has an attribute DNUM as a foreign key referring to DEPARTMENT_NO :</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>Thanks very much in advance and sorry for the long message.</p>