CODEWITHSUNDEEP
sql-server
Jack Allen
Jack Allen

Reputation: 104

Removing a primary key but keeping foreign key?

Before anyone says this is a duplicate question and down-votes: I know how to solve the issue but I would like the advice on which is the best way to go about it.

I have made a booking system, where employees can create bookings. The employees table has a primary key of their clock number, this is a foreign key in the bookings table because employees can only delete their own bookings unless they are an administrator.

Now the problem occurs when I want to remove an employee, but they have made 1 or more bookings in the system, obviously as I'm deleting the 'parent' the 'child' will want to be removed as well but I need to keep the entire history of bookings.

The solutions I have is to remove the foreign key constraint in the bookings table, so there is still a clock number but not a foreign key. Or to set something up with ON CASCADE NULL feature?

CREATE TABLE [dbo].[Employees](
    [ClockNo] [int] NOT NULL,
    [Forename] [varchar](20) NOT NULL,
    [Surname] [varchar](20) NOT NULL,
    [Email] [varchar](50) NOT NULL,
    [Department] [varchar](50) NOT NULL,
    [IsAdmin] [bit] NOT NULL,
    [Password] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [ClockNo] 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]


CREATE TABLE [dbo].[Bookings](
    [InvoiceNo] [varchar](40) NOT NULL,
    [ClockNo] [int] NOT NULL,
    [GateNo] [smallint] NOT NULL,
    [TruckNo] [smallint] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [Status] [varchar](20) NOT NULL,
    [Seal] [varchar](40) NULL,
    [ContainerNo] [varchar](40) NULL,
 CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED 
(
    [InvoiceNo] ASC,
    [GateNo] ASC,
    [StartTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

If there is a way without doing any of these things that would be great, as I would like the clock number to stay even if the employee has been removed so we can see who had made the booking, even if they no longer work for us

Upvotes: 0

Views: 67

Answers (1)

Christie
Christie

Reputation: 898

Best approach would be to have a flag in the Employee table as isDeleted and use that flag to maintain deleted employees. That way you will have records of all the orders of the deleted employees as well

Upvotes: 2

Related Questions