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.
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.
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;
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
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;