zero-or-one to zero-or-one

How do I model zero-or-one to zero-or-one relationship in Sql Server the most natural way?

There is a 'Hazard' table that lists hazards on a site. There is a 'Task' table for work that needs to be done on a site. Some Tasks are to fix a hazard, no task can deal with multiple hazards. Some Hazards have a task to fix them. No hazard can have two tasks associated with them.

The below is the best I could think of:

CREATE TABLE [dbo].[Hazard](
  [HazardId] [int] IDENTITY(1,1) NOT NULL,
  [TaskId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Hazard] PRIMARY KEY CLUSTERED 
(
  [HazardId] ASC
))

GO

ALTER TABLE [dbo].[Hazard]  WITH CHECK ADD  CONSTRAINT [FK_Hazard_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([TaskId])
GO

CREATE TABLE [dbo].[Task](
  [TaskId] [int] IDENTITY(1,1) NOT NULL,
  [HazardId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
  [TaskId] ASC
))

GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Hazard] FOREIGN KEY([HazardId])
REFERENCES [dbo].[Hazard] ([HazardId])
GO

Would you do that differently? The reason I'm not happy with this set up is that there need to be application logic to make sure that tasks and hazards point to each other and not to other task and hazards and that no task/hazard points to the same hazard/task another task/hazard points to.

Is there a better way?

You could go with your own idea of an asymmetric schema by removing one of the foreign keys from the current set-up, or, to keep things symmetric, you could remove both foreign keys and introduce a junction table with a unique constraint on each reference.

So, it would be like this:

CREATE TABLE dbo.Hazard
(
  HazardId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Hazard PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL
);

CREATE TABLE dbo.Task
(
  TaskId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Task PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL,
);

CREATE TABLE dbo.HazardTask
(
  HazardId int NOT NULL
    CONSTRAINT FK_HazardTask_Hazard FOREIGN KEY REFERENCES dbo.Hazard (HazardId)
    CONSTRAINT UQ_HazardTask_Hazard UNIQUE,
  TaskId int NOT NULL
    CONSTRAINT FK_HazardTask_Task FOREIGN KEY REFERENCES dbo.Task (TaskId)
    CONSTRAINT UQ_HazardTask_Task UNIQUE
);

You could additionally declare (HazardId, TaskId) to be the primary key if you need to reference these combinations from another table. For the purpose of keeping the pairs unique, however, the primary key is unnecessary, it is enough that each ID is unique.

StackExchange Database Administrators Q#111625, answer score: 9