Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create one to one relationship SQL server diagram

Tags:

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

like image 715
user384080 Avatar asked Oct 26 '10 06:10

user384080


People also ask

How do you write a one-to-one relationship in SQL?

One way to implement a one-to-one relationship in a database is to use the same primary key in both tables. Rows with the same value in the primary key are related. In this example, France is a country with the id 1 and its capital city is in the table capital under id 1.

How do I create a one-to-one map in SQL?

One-to-one (1:1) A relationship is one-to-one if and only if one record from table A is related to a maximum of one record in table B. To establish a one-to-one relationship, the primary key of table B (with no orphan record) must be the secondary key of table A (with orphan records).

How do I create a one-to-many relationship in SQL Server?

How to implement one-to-many relationships when designing a database: Create two tables (table 1 and table 2) with their own primary keys. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.


2 Answers

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

like image 124
Azhar Avatar answered Nov 30 '22 05:11

Azhar


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.

like image 29
IbrarMumtaz Avatar answered Nov 30 '22 05:11

IbrarMumtaz