Aben Aben - 3 months ago 15
SQL Question

Creating one-to-one relation using just one primary key

In my job, checking the database diagrams I have found a One-to-one relation between two tables, but the relation is not between two primary keys, the relation is between a primary key in one table and other non primary key attribute in the other table. In the database diagrams appear as "One to One relation". I wonder how can I create this kind of relationship "One to One" using just one primary on one table and using a non primary key in the other table.

enter image description here

Here are the scripts for "create" that I found in the database

---------------To create table Agreement Documents--------------

CREATE TABLE [dbo].[AgreementDocuments](
[AgreementDocumentID] [int] IDENTITY(1,1) NOT NULL,
[AgreementID] [int] NOT NULL,
[Document] [varbinary](max) NOT NULL,
CONSTRAINT [PK_AgreementDocuments] PRIMARY KEY CLUSTERED
([AgreementDocumentID] 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]
ALTER TABLE [dbo].[AgreementDocuments] WITH CHECK ADD CONSTRAINT
[FK_AgreementDocuments_Agreements] FOREIGN KEY([AgreementID])
REFERENCES [dbo].[Agreements] ([AgreementID])
GO
ALTER TABLE [dbo].[AgreementDocuments] CHECK CONSTRAINT
[FK_AgreementDocuments_Agreements]
GO


--------------------------To create table Agreements-----------------------

CREATE TABLE [dbo].[Agreements](
[AgreementID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NOT NULL,
[ClientID] [int] NOT NULL,
CONSTRAINT [PK_Agreements] PRIMARY KEY CLUSTERED
([AgreementID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO





If I run this two queries in a separate database, it creates two tables with "One to Many" relation. How is that possible?

Answer

You can make this a one-to-one relationship by adding a UNIQUE CONSTRAINT to the AgreementID field:

ALTER TABLE dbo.AgreementDocuments 
ADD CONSTRAINT uq_AgreementDocuments_AgreementId UNIQUE (AgreementId)

With the unique constraint, it will force it to be a one-to-one, rather than a one-to-many:

Before Constraint:

enter image description here

After Constraint:

enter image description here