References Key in SQL

Amudhan Kuppan

8y758SQL

please help me

use hospital_database
go
CREATE TABLE Department (
DepartmentID int NOT NULL,
Name varchar(30),
EmployeeID int ,
Primary Key (DepartmentID),
FOREIGN KEY (EmployeeID) REFERENCES Physician(EmployeeID)
);

this is my table Structure

my question is How will you cross check this

i need insert a data right?

try to enter the employeeid which is not phyisician table

i dont know please send me a query

and iam also give physician table structure:

EmployeeID int not null, it should be primary key

Name varchar(30),

Position varchar(200),

SSN Numeric(9,0)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Dear Amudhan,

I have noticed in the past that you have a tendancy of accepting answer even when you are not fully convinced and / or understood it.

Please DO NOT EVER do that. Please enguage in the conversation until you have fully understood / convinced about the given answer.

Please ALWAYS remember that even I might be wrong! If you do not argue you will not learn.
Amudhan Kuppan8y ago
yes iam understand small doubt so i will ask
ok thanks nithin
Nitin Sontakke8y ago
I just read you another question. If you want to do that, you can keep the HeadId in the department table, but keep it nullable.

Once you insert the employee in Physician table you UPDATE the Department table with an appropriate value for HeadId.

Hope you get the point. If not, please do let me know.
Amudhan Kuppan8y ago
ok thank you
Nitin Sontakke8y ago
You have created a table structure in a reverse fashion, which is not correct.

Just imagine, a department will have several employees then how can the department table can have employee Id column?

I don't know any scenario, where a employee can belong to more than one department. Will you agree?

If yes, your Employee table will have Department Id, as shown below:
use hospital_database
go

CREATE TABLE [dbo].[Department]
(
   [DepartmentID] int NOT NULL constraint [PK_Department] primary key clustered
  ,[Name] varchar(30)
);

CREATE TABLE [dbo].[Physician]
(
   [EmployeeID] int not null constraint [PK_Physician] primary key clustered
  ,[Name] varchar(30)
  ,[Position] varchar(200)
  ,[SSN] Numeric(9,0) -- why do you need it numeric? Keep it varchar! If a column having numeric info but NOT participating in arithmatic operations, keep it varchar.
  ,[DepartmentId] int not null constraint [FK_Physician_DepartmentId_Department_DepartmentId] foreign key references [dbo].[Department]([DepartmentID])
)