References Key in SQL

Question

hi 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)

Nitin Sontakke · 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 Kuppan · Answer

yes iam understand small doubt so i will ask ok thanks nithin

Nitin Sontakke · Answer

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 Kuppan · Answer

ok thank you

Nitin Sontakke · Answer

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]) )