Blog

In the simplest form, a relation in SQL is matching data in key columns with the same name in different tables. There are three types of relationships between tables. 

One-to-Many Relationship

Many-to-Many Relationships

One-to-One Relationships

 

One-to-Many Relationships

it is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.

 

Example:

 

CREATE TABLE dbo.Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(50),
ISBN VARCHAR(50)
);

CREATE TABLE dbo.Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName     VARCHAR(50),
MobileNo     CHAR(10),
Fk_Book_Id   INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);

INSERT INTO Book VALUES (1, 'C#', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'SQL','ISBN638242');
GO

INSERT INTO dbo.Author VALUES(100,'A','34303',1);
INSERT INTO dbo.Author VALUES(101,'B','453',1);
GO

SELECT * FROM dbo.Book;
SELECT * FROM dbo.Author;

 

 

Many-to-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both tables A and table B. Run the script below to see a king of many to many samples:

 

CREATE TABLE DimCustomers
(
  cust_id int PRIMARY KEY,

  cust_name varchar(50),
);
 GO
 CREATE TABLE DimAccounts
(
  acct_id int PRIMARY KEY,
  acct_name varchar(50),
  acct_type varchar(20),
);
GO
CREATE TABLE Customer_accounts_junction
(
  cust_id int,
  acct_id int,
  CONSTRAINT cust_acct_pk PRIMARY KEY (cust_id, acct_id),
  CONSTRAINT FK_Cust
     FOREIGN KEY (cust_id) REFERENCES DimCustomers (cust_id),
  CONSTRAINT FK_Acct
      FOREIGN KEY (acct_id) REFERENCES DimAccounts (acct_id),
);
GO
CREATE TABLE Fact_Transactions
(
  trans_id int PRIMARY KEY,
  trans_acct int,
  deposit int,
  CONSTRAINT FK_acct_id
      FOREIGN KEY (trans_acct) REFERENCES DimAccounts (acct_id),
);
GO

 

 

One-to-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints. Run this script in SQL server and see the result in the diagram:

 

CREATE TABLE dbo.Person
(
Pk_Person_Id INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);

CREATE TABLE dbo.PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);

INSERT INTO dbo.Person VALUES ('A','A@gmail.com');
INSERT INTO dbo.Person VALUES ('B','B@emails.com');


INSERT INTO dbo.PassportDetails VALUES (10, '4564533', 1);
INSERT INTO dbo.PassportDetails VALUES (11, '45645695', 2);

SELECT * FROM dbo.Person
SELECT * FROM dbo.PassportDetails;

 

References:

https://technet.microsoft.com/en-us/library/ms190651(v=sql.105).aspx

Example: http://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx