Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Difference between primary key and foreign key

Difference between foreign key and primary key:

In this post I will share key differences between foreign key and primary key.

Primary Key:

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

Foreign key:

Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Differences:

Following table shows all possible differences between MS-SQL Server table primary key and foreign key.

Difference

Primary Key

Foreign Key

Limit

We can have only one Primary key in a table

We can have more than one foreign key in a table.

Null values

Primary Key can't accept null values.  

Foreign key can accept multiple null values.

Index

By default, Primary key is clustered index.

You can manually create an index (non-clustered) on foreign key.

 

Auto Increment

Primary key supports Auto Increment value.

Unique Constraint doesn't support Auto Increment value. 

Definition

The PRIMARY KEY uniquely identifies each record in a database table

Foreign key is a field in the table that is primary key in another table.

 

  धन्यवाद मित्रानोआपला प्रत्येक दिवस आनंदी आणि सुखी जावो  

SQL Server Concurrency

In this article I will share sql server concurrency and concurrency issues in detail.

SQL Server Concurrency

Database concurrency ensures that when multiple operations are occurring at once,then different operations will play nicely together.

Main aim of concurrency is improving database performance.

Sql server provided set of rules and constraints that coordinate the behaviours of transactions, making sure that different operations will play nicely together.

Concurrency control

When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.

Types:

Pessimistic concurrency control:

In pessimistic locking a record or page is locked immediately when the lock is requested.

The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time.

Optimistic concurrency control:

In an optimistic lock the record or page is only locked when the changes made to that record are updated.

There are four types of concurrency issues

1.  Lost Update

Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.

2. Dirty Reads

Reading uncommitted modifications are calling Dirty Reads. Values in the data can be changed and data is changing in the data set before the end of the transaction, thus getting you incorrect or wrong data.

Protecting transactions from dirty reads:

a. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

b. The SNAPSHOT isolation level.

Example:

---Query1
BEGIN TRAN 
UPDATE Employee SET CreatedDate = getdate()
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05' 
ROLLBACK  
 
---Query 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Employee 

 

Query 1 changes the row, but actually query 1 will roll-back all changes after wait time completion.
Read uncommitted isolation level allows user to read uncommitted (wrong) data updated by query 2

3.  Nonrepeatable Reads

Nonrepeatable reads occur when a second transaction accesses the same row several times and reads different data every time. This involves multiple reads of the same row. Every time, the information is changed by another transaction.

Example:

Open Microsoft SQL Management Studio. Open 2 tabs and start a transaction in both.
Select data in first window and modify it in second and commit changes.
Then, re run previous select. Data has been changed. This is non repeatable read issue.

 4. Phantom Reads

Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.

This problem occurs when UPDATE/DELETE is happening on one set of data and INSERT/UPDATE is happening on the same set of data leading inconsistent data in earlier transaction when both the transactions are over.

Example:

--Query1
SET TRANSACTION ISOLATION
LEVEL REPEATABLE
READ   
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:10' 
SELECT * FROM Employee 
ROLLBACK 
 
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
 
VALUES
('shourya kendre','England','English','London',5) 
Query one first select statement returns actual data, wait time completion sql query two inserts wrong data and select query 1 returns wrong data.