Ehsan Ghanbari

Experience, DotNet, Solutions

All kinds Relations in sql server

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:

  1. CREATE TABLE dbo.Book
  2. (
  3. Pk_Book_Id INT PRIMARY KEY,
  4. Name VARCHAR(50),
  5. ISBN VARCHAR(50)
  6. );
  7.  
  8. CREATE TABLE dbo.Author
  9. (
  10. Pk_Author_Id INT PRIMARY KEY,
  11. FullName     VARCHAR(50),
  12. MobileNo     CHAR(10),
  13. Fk_Book_Id   INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
  14. );
  15.  
  16. INSERT INTO Book VALUES (1, 'C#', 'ISBN3030303');
  17. INSERT INTO Book VALUES (2, 'SQL','ISBN638242');
  18. GO
  19.  
  20. INSERT INTO dbo.Author VALUES(100,'A','34303',1);
  21. INSERT INTO dbo.Author VALUES(101,'B','453',1);
  22. GO
  23.  
  24. SELECT * FROM dbo.Book;
  25. 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 table A and table B.

Run the script below to see a king of many to many sample:

 

  1. CREATE TABLE DimCustomers
  2. (
  3.   cust_id int PRIMARY KEY,
  4.  
  5.   cust_name varchar(50),
  6. );
  7.  GO
  8.  CREATE TABLE DimAccounts
  9. (
  10.   acct_id int PRIMARY KEY,
  11.   acct_name varchar(50),
  12.   acct_type varchar(20),
  13. );
  14. GO
  15. CREATE TABLE Customer_accounts_junction
  16. (
  17.   cust_id int,
  18.   acct_id int,
  19.   CONSTRAINT cust_acct_pk PRIMARY KEY (cust_id, acct_id),
  20.   CONSTRAINT FK_Cust
  21.      FOREIGN KEY (cust_id) REFERENCES DimCustomers (cust_id),
  22.   CONSTRAINT FK_Acct
  23.       FOREIGN KEY (acct_id) REFERENCES DimAccounts (acct_id),
  24. );
  25. GO
  26. CREATE TABLE Fact_Transactions
  27. (
  28.   trans_id int PRIMARY KEY,
  29.   trans_acct int,
  30.   deposit int,
  31.   CONSTRAINT FK_acct_id
  32.       FOREIGN KEY (trans_acct) REFERENCES DimAccounts (acct_id),
  33. );
  34. 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 diagram:

 

  1. CREATE TABLE dbo.Person
  2. (
  3. Pk_Person_Id INT IDENTITY PRIMARY KEY,
  4. Name VARCHAR(255),
  5. EmailId VARCHAR(255),
  6. );
  7.  
  8. CREATE TABLE dbo.PassportDetails
  9. (
  10. Pk_Passport_Id INT PRIMARY KEY,
  11. Passport_Number VARCHAR(255),
  12. Fk_Person_Id INT UNIQUE FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
  13. );
  14.  
  15. INSERT INTO dbo.Person VALUES ('A','A@gmail.com');
  16. INSERT INTO dbo.Person VALUES ('B','B@emails.com');
  17.  
  18.  
  19. INSERT INTO dbo.PassportDetails VALUES (10, '4564533', 1);
  20. INSERT INTO dbo.PassportDetails VALUES (11, '45645695', 2);
  21.  
  22. SELECT * FROM dbo.Person
  23. 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



An Introduction to basic concepts of SQL Server

I'm going to write about basic concepts of Microsoft  SQl Server in two part this is the part one! , as I've rarely seen SQL graphical text tutorial (most of them are text only and it does not make good sense for beginners like me), I want to fit more and more picture in this Article! This article is about SQl language fundamentals , and obviously it is familiar for most of the developers!

Introduction to SQl language 

SQL is a language designed to retrieve and management of data in relational databases , SQL is a language and not all relational databases support SQL , so if you have SQl management studio installed in your system , let's begin!

CRUD operation 

Create , read , update and delete are the most common operation in most of software systems and in every Enterprise application.

                  ff

Right click on Databases and create a new database , name it and then right click on that tables and create a table :

dd

     

Now , create the table's field . add ID, FirstName ,LastName ,UserName and Password ,

As shown in the picture below :

d

    

None of the field accept the null, therefore I leaved all of field definition as unchecked.

Now it is need to make ID field of this table as a PrimaryKey (every table should have a field as primary key as Identity of every record )

 d

Then make the ID field's  Identity property as yes :

d

 

Now , save (ctrl+s) your table and name it Users , you can see your table in object explorer after refreshing , like this :

d

Now , let's insert some data to our table , to do this right click on table and select Edit top 200 rows to Add , go thought picture below :

 d

Open the table and insert the data , like this :

d

Now to writing CRUD(create , read , Update and delete) operation , you need to create a query , to do this click on new query and begin writing query :

d

First , create how to read from table with query language , type code below in query you just created and run(execute) it :

  1. Select *
  2. from Users

Picture shows the details!

d

Run another select query to select just UserName and Password, like this :

  1. Select UserName,Password
  2. from Users

Run the query to see the result , looks like this one , yah ?

You just made two select query , rewrite these query and run them and see the result :

  1. Select UserName,Password
  2. from Users
  3. where (ID=2)

Note : you can use Equal(=) , greater than(>), less than(<) , greater than and equal (>=) , less than ,equal (<=)  operand in your where condition.

  1. Select UserName,Password
  2. from Users
  3. where (ID between 1 And 2)
  1. Select FirstName, UserName, Password
  2. from Users
  3. where (ID IN (1,2,3,4))
  1. Select *
  2. from Users
  3. where ID <> 1
  4.  
  1. Select *
  2. from Users
  3. where (UserName='Ehsan')

LIKE keyword , try this one :

  1. Select *
  2. from Users
  3. where (FirstName LIKE 'Ehsan')

You can order the query by "order by"

  1. select * from Users
  2. order by "LastName"

There are lots of SQl keyword that you can test them 

Top : returns the number of the records ,for example if you want to return 3 number of record in a table you can use TOP key work ,see the example

  1.          Select TOP 3 from Users

Between : used after where clause to customize a select list, for example try this one

  1. Select Top 5 from Users
  2. Where ID between 1 and 3 

Alias : you can name a column or table by alias keyword  , like below

  1. Select Top 3 from Users
  2. Where ID between 1 and 3
  3. As TopBest 

SQL has some built in function , you also can use them in your queries ,take a look at this :

  1. Select *
  2. from Users
  3. where (len(FirstName)>4) AND (Len(LastName)<6)

Len : is a function the returns the length!

Deleting rows in a table one by one could cause some performance issues when you want to completely clear the table , in this case you can use truncate to clear the table . Notice that truncate doesn't log anything! Look at the syntax :

  1. Truncate table Users 

you can although create table in sql server not only by wizard ,but with code with syntax :

  1. Create Table Users
  2. (
  3. UserId   int,
  4. UserName varchar(30),
  5. Password  varchar(20),
  6. CreationDate datetime,
  7. phoneNumber   int  null
  8. )

So , if you remember we created the table data by wizard Not by query now create another query to add data ( by the way every time after creating query save it to use it in next steps ) , type this code in your new query :

  1. Insert into Users
  2. (FirstName,LastName,UserName,Password)
  3. values ('randy','colson','randy','454574')

To see the inserted row in Users table , right click on Select top 1000 rows :

d

So let's dig in Update operation

I want to Update the FirstName and UserName of last record I added with Id=3 , to do this :

  1. Update Users
  2. Set FirstName='michael', UserName='michaelAccount'
  3. where ID='4'

And the result of this query :

d

Delete a row from table is pretty simple , for example :

  1. Delete from Users where ID=1

As you can see in deleting and updating a table content you should have the table's Id .You saw the simple CRUD operation.



About Me

Ehsan Ghanbari

Hi! my name is Ehsan. I'm a developer, passionate technologist, and fan of clean code. I'm interested in enterprise and large-scale applications architecture and design patterns. I spend a lot of time on software architecture. Since 2008, I've been as a developer for different companies and organizations and I've been focusing on Microsoft ecosystem all the time. During the past years, Read More

Post Tags
Pending Blog Posts
using Elmah in asp.net MVC4
Using FluentSecurity in MVC
Strategic design
Factory Pattern
time out pattern in ajax
Redis as a cache server
How to use PagedList In asp.net MVC
Multiple submit buttons in asp.net MVC
Domain driven design VS model driven architecture
What's the DDD-lite?