CODEWITHSUNDEEP
.netsql-server
user384080
user384080

Reputation: 4682

How to create one to one relationship SQL server diagram

Does anyone know how to create a one-to-one relationship from a SQL Server database diagram?

Upvotes: 24

Views: 26034

Answers (6)

PatsonLeaner
PatsonLeaner

Reputation: 1250

It's very pretty straight forward when you use the following:

ALTER TABLE [Salary]
ADD CONSTRAINT FK_Salary_Employee FOREIGN KEY([EmployeeID]) 
    REFERENCES [Employee]([ID]);

That should do the trick!!!

Upvotes: 0

Nugs
Nugs

Reputation: 5783

I believe the question was specifically around how to create a one-to-one relation in SQL Server Management Studios Diagram window. I have a procedure where you can create a one-to-one relation in SQL Server Management Studio database diagram.

  1. Create two tables (A and B), leave table B without a primary key.
  2. Drag a relation from table A primary key to table B's referenced column (Any matching type column, any name) (This will initially create a one-to-many relation
  3. Save the diagram (If you do not save here you will likely get a conflict in the change script)
  4. Go to table B and right click the column and set it as a primary key (This will change the relation to a one-to-one)
  5. Save the diagram

You're done!

Upvotes: 0

Umanda
Umanda

Reputation: 4843

You can easily use MSSQL Management Studio.

ie

table_user

uid(pk) username email

table_profile

pid(pk) f_name l_name user_id (fk)

  1. Right click on user_id (fk).
  2. Indexes / Keys...
  3. you can get Indexes / Keys window.

then follow this,

4 Choose "Add" (From Indexes / Keys window, it will add a new name )

5 In "General section" select "Columns" and then select "user_id"

6 In "General section" set "Is Unique" as a true

7 In "Identity section" give a name for (Name) section. in this case I ll give UK_user_id_profile

8 after all those above steps, close Indexes / Keys window

9 Drag and Drop "uid" (from user table) into "user_id" (from profile table)

That is it.

The theory behind that, foreign key should be a unique .

You need to put a unique key constraint on top of the foreign key, so its restricted to one-one relationship.

That is what Azam said in his post.

[In-case, I am using MSSQL 2012]

Cheers.

Upvotes: 2

Andamon A. Abilar
Andamon A. Abilar

Reputation: 171

if your table is created like this,

CREATE TABLE tableName (
   id INT NOT NULL IDENTITY(1,1) CONSTRAINT[PK:tableName] PRIMARY KEY(id)
      , fkId INT NOT NULL
           CONSTRAINT[FK:tableName:tableFk]
           FOREIGN KEY(fkId)
           REFERENCES tableFk(id)
)

CREATE TABLE tableFk (
   id INT NOT NULL IDENTITY(1,1) CONSTRAINT[PK:tableFk] PRIMARY KEY(id)
)

you can alter the tableName with this code to set tableName.fkId as unique 

ALTER TABLE tableName
    ADD UNIQUE (fkId)

Upvotes: 2

IbrarMumtaz
IbrarMumtaz

Reputation: 4393

Assuming you are using Sql Server 2008 onwards and SSMS.

  1. The easiest way to do this is simply grab the key from the primary table in the designer surface and drop the key over the secondary table, directly over the primary key.
  2. A second later, two dialog boxes will pop up, in the active dialog confirm that the two keys involved in the drag and drop process, are actually P.Ks in their respective tables and press O.K.
  3. Finally, confirm in the second tables any constraints and any other settings that you would want applied like cascading features and etc.

Once you click O.K again for the second time, SSMS Designer will show a key to key relationship between the tables involved. This is a one-one relationship. Read as, one record in one table directly relates to another record in another table.

i.e A ResolutionsTable - 1 : 1 - Resolution Types Read as one resolution has one resolution type applied to it, i.e, case closed or case ongoing, case pending law change, case denied? Of course people have different DB design skills so what works for one may not work for another db developer. Never the less the example is clear enough to understand.

Hopefully this will help any non sql syntax savvy noobies out there like me who prefer to do build an entire database via the SQL Database Diagram feature.

Upvotes: 8

Azhar
Azhar

Reputation: 20670

You need to put a unique key constraint on top of the foreign key, so its restricted to one-one relationship.

Upvotes: 43

Related Questions