Foreign key reference for 1 to 1 relationship in SQL

Recently we had a situation to design one to one relationship between 2 tables. One of our developer confused, need to create a quick demo to explain the design.

Primary Table can be created with ID as identify and the same column valued can be used in secondary table and include a foreign key reference from secondary to first table. Also add a Unique or primary constraint on Secondary table to avoid duplicate records.

FYI:
ForeignKeyReference

Quick Demo Script for reference:

use tempdb

IF OBJECT_ID('[dbo].[session_d]', 'U') IS NOT NULL
DROP TABLE[dbo].[session_d] 
go
CREATE TABLE session_d(sessionid int
, session_Col2 varchar(2)
, primary key (sessionid))

IF OBJECT_ID('[dbo].[session]', 'U') IS NOT NULL
DROP TABLE[dbo].[session] 
go
CREATE TABLE [session](sessionid int identity
, session_Col1 varchar(2)
, primary key (sessionid))


-- Reference suggested
ALTER TABLE [dbo].[session]  WITH CHECK ADD  CONSTRAINT [Session_SessionId] FOREIGN KEY([SessionId])
REFERENCES [dbo].[session_d] ([SessionId])
GO

-- inserting record into Session will fail, it expects SessionID in Session_d first (not a good design) 
insert into [session] values ('a')
-- Error 
GO

-- Drop existing constraint and add Reverse reference 
ALTER TABLE [dbo].[session]  drop CONSTRAINT [Session_SessionId]
GO

ALTER TABLE [dbo].[session_d]  WITH CHECK ADD  CONSTRAINT [Session_d_SessionId] FOREIGN KEY([SessionId])
REFERENCES [dbo].[session] ([SessionId])
GO

-- adding records first into session table and session_d
insert into [session] values ('a')
insert into [session_d] values (@@IDENTITY,'b')

insert into [session] values ('c')
insert into [session_d] values (@@IDENTITY,'d')

insert into [session] values ('e')
insert into [session_d] values (@@IDENTITY,'f')

select * from [session]
select * from [session_d]
--
Advertisements
This entry was posted in Script, SQL Server and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s