CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2relation
Aben
Aben

Reputation: 87

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?

Upvotes: 1

Views: 2897

Answers (2)

Siyual
Siyual

Reputation: 16917

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

Upvotes: 1

M T Head
M T Head

Reputation: 1290

Create table one with an identifying ID column. AgreementDocumentID is identity and distinct

In the other table do a foreign key to the first table AgreementDocumentID field. And then put a distinct index on the second table for AgreementDocumentID.

If I understand your question.

No Er-Digram tool works 100% you have to edit what they create to make it right. Depending on the tool quality your amount of edits change.

Upvotes: 0

Related Questions