5

I have a table Nurse:

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [WardId]     [int]               NOT NULL,
 CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and a table Employee:

CREATE TABLE [dbo].[Employee](
    [Id]            [int] IDENTITY(1,1) NOT NULL,
    [Name]          [nvarchar](100)     NOT NULL,
    [StaffNumber]   [nvarchar](50)      NOT NULL,
    [Salary]        [money]             NOT NULL,
    [Address]       [nvarchar](max)     NOT NULL,
    [GenderId]      [int]               NOT NULL,
    [ContactNumber] [nvarchar](20)      NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

How do I establish a 1:1 relationship between the two tables?

There is one nurse for one employee, only the Nurse table is separated from the Employee table as it has to be part of relationships, such as, just any employee, e.g. a Nurse belongs to a Ward, while a Doctor, also an employee, belongs to a Department.

10

I guess that you mean: "every Nurse is an Employee".

This can be done with (keeping the Employee table as it is):

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,        -- why not name this EmployeeId ?
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
    ( [Id] )
 ) ;

and changing the Nurse table so the primary key is not an IDENTITY and it has a foreign key that references the Employee (Id):

CREATE TABLE [dbo].[Nurse](
    [EmployeeId] [int] NOT NULL,                 -- not IDENTITY !
    [WardId] [int] NOT NULL,
  CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED 
    ( EmployeeId ),
  CONSTRAINT [FK_Employee_Nurse] FOREIGN KEY  
    ( EmployeeId )
    REFERENCES dbo.Employee ( Id )
) ;
  • Thank you, I will check this out later. I must revisit some very old code to do so. Employee.Id is named such because all my pure PKs are always named only Id. It is generally accepted, and also a very simple and useful convention for me, not to mention one recognised by EF conventions at the time of my OP. It cannot be ambiguous, neither in queries only involving the Employee table, nor in multi-table queries, where T-SQL syntax will require a qualifying table - or alias - prefix. – ProfK Feb 23 '17 at 2:34
  • 1
    Thank you for saying to remove the identity value from Nurse! That is exactly the right way to handle this. – ErikE Feb 23 '17 at 2:50
1

Seems all you need to do is to add a primary key to your Nurse table and add a unique' foreign key relationship from the Nurse table to the Employee table;

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [StaffNumber] [nvarchar](50) NOT NULL,
    [Salary] [money] NOT NULL,
    [Address] [nvarchar](max) NOT NULL,
    [GenderId] [int] NOT NULL,
    [ContactNumber] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))

GO
CREATE TABLE [dbo].[Nurse](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] FOREIGN KEY REFERENCES EMPLOYEE(ID),
    [WardId] [int] NOT NULL,
 CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))
GO
CREATE UNIQUE INDEX UQ_NURSE_EMPLOYEEID ON dbo.Nurse ( EmployeeId )
  • 1
    But leaving that Id column in the Nurse table seems like a serious problem waiting to happen! There's absolutely no need for it in there. If you want to have an FK to a Nurse, you call it NurseId and put the FK to the Nurse table. Re-aliasing a surrogate key to an arbitrary second surrogate key is bound to cause problems. Think of how when that identity column is not present and you have a NurseId, you can join directly to the Employee table with it! Why force a join through Nurse with a needless layer of abstraction that had no benefits? – ErikE Feb 23 '17 at 2:23
  • 1
    @ErikE Nobody has even mentioned needing an FK to Nurse. I rather had in mind an FK in Nurse that references Employee. Without the Id column, Nurse has no surrogate PK at all, so how could introducing Id imply a second surrogate PK? A single, purely surrogate, PK is in no way at all a "needless layer of abstraction". – ProfK Feb 23 '17 at 2:44
  • @ProfK Somebody did too mention needing an FK to Nurse: I did. I think you're not understanding what I'm saying. The entity in question, the Nurse, is a "subclass" of Employee and thus already has a surrogate PK, the identity value from Employee. Adding another one gives each Nurse two synthetic keys: Employee.Id and Nurse.Id. This is a completely needless, and probably harmful, extra key. If this were a C# class public class Nurse : Employee { }, how would you expose the Nurse Id, and why would you need to since it already has Id` by virtue of being a subclass of Employee? – ErikE Feb 23 '17 at 2:48
  • The relationship between Employee and Nurse is "1 to 0-or-1". Such relationships require no Id int identity(1,1) column in the second table. Doing so is just a habit on the part of people who aren't thinking clearly about what the larger relation is between the two tables (after they have been joined). – ErikE Feb 23 '17 at 2:49
  • 1
    @ProfK Who said anything about ORMs? The extra Id is harmful because 1. It will eventually hurt performance by forcing an extra, unneeded join somewhere, 2. It will prevent an FK from being created, and 3. It can make it very difficult to copy data from one set of rows as a template to another. I have personally seen all three of these in a database and regretted the double artificial key a lot. You may think differently, but if you agree it's not necessary, then you should agree it's bad to add unnecessary data. – ErikE Feb 23 '17 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.