CODEWITHSUNDEEP
sql-serversql-server-2008foreign-keyssql-server-2008-r2one-to-one
tugberk
tugberk

Reputation: 58484

How can I force one to one relationship on SQL Server 2008 or 2008 R2

Here is my scenario on SQL Server 2008 R2:

This is my first table:

CREATE TABLE [dbo].[Foos](
    [FooId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Foos] PRIMARY KEY CLUSTERED 
  (
    [FooId] ASC
  )
) ON [PRIMARY]

This is the second table which has a relationship to Foos table:

CREATE TABLE [dbo].[Bars](
    [BarId] [int] IDENTITY(1,1) NOT NULL,
    [FooId] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Bars] PRIMARY KEY CLUSTERED 
  (
    [BarId] ASC
  )
) ON [PRIMARY]

Go

ALTER TABLE [dbo].[Bars]  WITH CHECK ADD  CONSTRAINT [FK_Bars_Foos] FOREIGN KEY([FooId])
REFERENCES [dbo].[Foos] ([FooId])
ON DELETE CASCADE
GO

But it is not one to one. What should I do to force this to be one to one relationship? Should I use check constraints?

Upvotes: 1

Views: 4126

Answers (2)

Pankaj
Pankaj

Reputation: 10115

You can keep Identity column(BarID) also. Then Unique key will help you out from this problem.

   IF NOT EXISTS(SELECT OBJECT_ID from sys.objects WHERE name ='foo_bars')
alter table bars add constraint foo_bars unique(fooid)

Upvotes: 0

gbn
gbn

Reputation: 432471

Add a unique constraint to FooId in Bars.

However, you don't need BarID then because they have the same key. So it looks like this

CREATE TABLE [dbo].[Bars] (
    [FooId] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,

    CONSTRAINT [PK_Bars] PRIMARY KEY CLUSTERED (FooId),

    CONSTRAINT [FK_Bars_Foos] FOREIGN KEY([FooId]) 
           REFERENCES [dbo].[Foos] ([FooId])
           ON DELETE CASCADE
)
GO

However again, you don't need Bars at all: it is one table...

Upvotes: 3

Related Questions