Constraint Kullanarak Veri Bütünlüğü Sağlamak

Merhaba arkadaşlar,

Bu yazımda sizlere veri bütünlüğünün ne olduğunu ve kısıtlayıcı(constraint) kullanarak veri bütünlüğünü nasıl sağladığımızdan bahsetmek istiyorum.

Veri Bütünlüğü Nedir?

Veri bütünlüğünü, ilişkisel veri tabanlarında yanlış verilerin girişini önlemek, tablolara yaptığımız insert, delete ve update işlemlerinde verilerin ilişkili tablolarla uyumluluğunu ve veri bütünlüğünü sağlamak için yapmaktayız.Örnek vermek gerekirse;

Şirket çalışanlarının bilgilerinin tutulduğu bir tablo düşünelim. Bu tabloda;

• ÇalışanID sütununa girilen verilerin benzersiz olması
• Çalışan isim ve soyadlarının 12 karakterden fazla olmaması
• DepartmanID'lerin ilişkili başka tablodaki verilerle bütünlüğün sağlanması gibi.

Veri bütünlüğünü sağlarken bazı yöntemlerden faydalanmaktayız. Bu yöntemler; constraint, rule ve default, trigger, stored procedure ve sql server dışından veri bütünlemesi yapmak için kullanılan programlama dillerinden oluşmaktadır. Bu yöntemler iki ana başlıkta toplanmaktadır.

• Tanımlanabilir Veri Bütünlüğü
• Prosedürel Veri Bütünlüğü

Yukarıdaki resimde görüldüğü üzere yukarıdan aşağıya doğru giderken fonsiyonellik artarken performansta bir düşüş yaşanmaktadır.

Bu yöntemleri seçerken dikkat etmemiz gereken nokta:

Her zaman çözümü en alt katmanda çözmeye çalışmalı, eğer çözülemiyorsa bir üst katmana giderek çözüm aramalıyız.

Bunun nedeni üst katmanlara çıkıldıkça SQL Server'a daha fazla yükün binmesi.

Örnek vermek gerekirse;

Veri bütünlüğünü constraint kullanarak halledebilecek iken trigger kullanmak.

Trigger'lar constraintlerin aksine transaction sonrası devreye girerler. Yani veriler üzerinde bozulma olup olmadığını işlem bittikten sonra kontrol ederler. Eğer veri bütünlüğünde bir bozukluk yoksa hiçbir sorun olmayacaktır. Fakat veri bütünlüğünde bir sıkıntı varsa transaction sonrası bunun için SQL Server'a ekstra bir iş yükleneceğinden gereksiz bir performans kaybına neden olabiliriz.

Tanımlanabilir Veri Bütünlüğü

Constraint'ler rule ve default'lardan oluşan ve oluşturulan nesneleri kullanarak sağlanan veri bütünlüğüdür. Kullanımın basit olması açısından avantajlı olmakla beraber dışarıdan fazla müdahele edilememesi açısındna dezavantajıdır.

Tanımlanabilir veri bütünlüğü 3 esasa dayanmaktadır.

Varlık(Entity) Bütünlüğü

Bir tabloya girilen her farklı satır için bir ayırıcı olmasını sağlamaktadır.(primary key)

Alan(Domain) Bütünlüğü

Tablodaki sütunların hangi değerleri alabileceği, bu sütunların null değer alıp alamayacağını sağlamaktadır.

Referanssal(Referential) Bütünlük

Primary ve foreign key kullanarak birbirine bağlı tablolardan foreign keyli tablo üzerinde silinmeyen tek bir kayıt dahi olduğu sürece ana tablo üzerinden silme işleminin yapılamayacağını garanti eder. Yani bir anne kaydı silmek için bu kayda ait bütün çocuk kayıtları silmek gerekmektedir.

Constraint

Kelime anlama kısıtlama olan constraintler, veri üzerinde mantıksal sınırlama yapmak için kullanılmaktadır. Constraintler tablo tanımlanırken veya tablo tanımlandıktan sonra oluşturulabilmektedir. Rule ve Default'ların constraint'lerden farkı tablo dışında ayrı bir nesne olarak tanımlanmasıdır. Rule ve Default'un yaptığı her işlemi constraint kullanarak yapabilmekteyiz. SQL Server'ın Rule ve Default'a hala destek vermesinin geriye dönük uyumdan dolayıdır. Rule'un yaptığı işi check constraint, Default'un yaptığı işi default constraintle yapabiliriz.

Primary Key Constraint

• Birincil anahtar kısıtlayıcısıdır.
• Null değer alamaz
• Aynı olmayan değerlerin girilmesini sağlar.

Örnek vermek gerekirse;

musterid sütununa primary key constraint tanımlamak istersek

Constraint pk_musterid primary key (musterid)

Unique Constraint

• Tekil anahtar kısıtlayıcısıdır.
• Tablodaki sütunlara unique değer girmek için kullanılır.
• Boş değer alabilir ve tablodaki birden fazla sütunda kullanılabilir.

Örnek vermek gerekirse;

tcno sütununa unique key constraint tanımlamak istersek

Constraint uq_tcno unique (tcno)

Foreign Key Constraint

• Yabancıl anahtar kısıtlayıcısıdır.
• Bir tablodaki bir sütuna ait verilerin başka bir tablonun  belirli bir sütunundan gelmesini denetler.

Constraint fk_musterid foreign key (musterid) references tablo1(musterid)

Delete Update işlemleriyle Foreign Key Kullanımı

İlişkili tablolar üzerinde delete update işlemi yaparken bağlı tablo üzerinde de bazı işlemlerin geçerli olmasını isteyebiliriz. Bunun için aşağıdaki deyimleri kullanmaktayız.

CASCADE: Foreign key tanımlı tabloya aynı aksiyonu uygular.

SET NULL: Foreign key kolonlarını null değerlerine ayarlar.

SET DEFAULT: Foreign key kolonlarını default değerine ayarlar.

Constraint fk_musterid foreign key (musterid) references tablo1(musterid) on delete [castade|set null|set default] on update [castade|set null|set default]  

Default Constraint

• Bir sütuna önceden belirlenmiş bir değer atamak için kullanılır.
• insert işleminde geçerlidir.

Örnek vermek gerekirse; tarih bilgisi girilmediğinde tarih sütununa getdate() fonksiyonuyla tarih sütununa veri girişi yapmak istiyoruz.

Constraint df_tarih default (getdate()) for tarih

Check Constraint

• Kontrol kısıtlayıcısıdır.
• Belirlenen formata göre veri girişi yapmak için kullanılır.

Örnek vermek gerekirse; numara sütununa 100 rakamından büyük değerlerin girmesini istediğimizde

Constraint chck_no check (numara>100)

Bu constraintleri silmek istersek bunu drop komutuyla yapmaktayız.

drop constraint contraint_ismi

 sp_helpconstraint tablo_adı stored prosedürünü kullanarak tablo üzerindeki tanımlanan kısıtlayıcıları listelemek mümkündür.

Konu ile alakalı videom:

Improving Query Performance

Herkese merhaba,

Bugün sizlerle, SQL'de yazdığımız sorgularımızın performansı ile ilgili çektiğim videoyu paylaşmak istiyorum.
Çekmiş olduğum videomda;

• Sorgu yazarken performans açısından neler yapabiliriz, nelere dikkat etmeliyiz.
• Database engine bir sorguyu çalıştırırken nasıl bir yöntem izlemektedir.
• Yazılan sorguların performans takibini nasıl yapmaktayız. 

gibi konuları ele aldım.

 Konu ile ilgili videom:

Görüşmek üzere, kendinize iyi bakın.

Set Operators

Herkese merhaba,

Bugün sizlere  veritabanımızda kullandığımız set operatörlerinden bahsedeceğim.

Set operatörleri iki veya daha fazla sorguyu kümelemek için kullanılmaktadır.

Bu set operatörlerini kullanarak birleştirdiğimiz sorgulara bileşik sorgu yani compound queries denmektedir.

Sorgularımız arasındaki çeşitli işlemler için farklı set operatörleri vardır.
Bunlar;

• UNION, UNIONALL
• EXCEPT, INTERSECT
• APPLY, CROSS APPLY, OUTER APPLY

UNION ve UNION ALL Set Operatörleri

Bu operatör iki veya daha fazla sorguyu tek bir tablo olarak bize geri döndürür. 

Bu operatörleri kullanırken dikkat etmemiz gereken önemli bir nokta var:

Karşılaştırma yaptığımız iki sorgu arasındaki ifadelerin türleri aynı olmalıdır.

Yani bir tablonun nvarchar türündeki bir sütunuyla diğer tablonun int türündeki bir sütunu birleştirip tek bir tablo olarak geri döndüremeyiz.

UNION operatörü karşılaştırılan ifadeleri getirirken DISTINCT komutuyla aynı işlemi yaparak tekrar eden ifadeleri bize geri döndürmezler. Eğer bu ifadeleri de görmek istiyorsak UNION ALL deyimini kullanmalıyız.

Union Syntax:

<query_specification>
UNION [ALL]
<query_specification>

 Örnek vermemiz gerekirse;

Bir şirketin içerisindeki çalışanlar veritabanımızda çalıştıkları departman tablolarında ayrı ayrı tutuluyor olsun. Bu departman tablolarını Departman1 ve Departman2 olarak isimlendirelim.

Biz bu şirket çalışanlarını bir tablo altında listelemek istediğimizde UNION operatörünü şu şekilde kullanıyoruz.

select Name as Calısanlar from Departman1 

UNION

select Name from Departman2

Bu iki sorguyu UNION operatörüyle birleştirip tek bir tabloda Calısanlar adlı sütunun altında şirket çalışanlarını listelemiş olduk.

EXCEPT, INTERSECT

Except ve Intersect operatörleri belli kurallara göre iki sorgu arasında karşılaştırma yapmak için kullanılmaktadır.

Except Operatörü: İki sorgu arasında karşılaştırma yaparken, ilk sorgudaki tabloda yer alan fakat ikinci sorgudaki tabloda yer almayan ifadeleri bize geri döndürür. Yani tablolar arasındaki farkı görmek istediğimizde Except operatörünü kullanmamız gerekmektedir.

Except Syntax:

<query_specification>
EXCEPT
<query_specification>

Intersect Syntax:

<query_specification>
INTERSECT
<query_specification>

Örnek vermek gerekirse;

Müşterilerimiz ve bu müşterilerimizin siparişlerinin tutulduğu iki ayrı tablomuz olsun. Biz bu iki tabloyu karşılaştırarak sipariş vermemiş müşterilerimizi tek bir tabloda  EXCEPT operatörünü kullanarak görüntüleyebiliriz.

select CustomerID from Sales.Customer

EXCEPT 

select CustomerID from Sales.SalesOrderHeader

Except operatörünü kullanarak iki tablo arasındaki CustomerID'leri karşılaştırdık ve Customer tablosunda olup SalesOrderHeader tablosunda olmayan CustmerID'leri listeledik. Yani sipariş vermemiş müşteriler listelendi.

Intersect Operatörü: İki sorgu arasında karşılaştırma yaparken, karşılaştırdığımız ifadelerin iki tablodada mevcut olması durumundaki verileri bize tek bir tabloda geri döndürür. Bir önceki örnek üzerinden açıklayacak olursak;

select CustomerID from Sales.Customer

INTERSECT 

select CustomerID from Sales.SalesOrderHeader

Bu operatörü kullanarak iki tabloda da var olan CustomerID 'leri listeledik. Bu durumda listelenen CustomerID 'ler aynı zamanda sipariş veren müşterileri belirtmektedir.

APPLY, CROSS APPLY, OUTER APPLY

SQL 2005 ile birlikte gelmiş olan APPLY operatörü tvf'lerle join işlemi yapar. Yani biz bu operatörle

Tablomuzdaki veriyi fonksiyon içerisine parametre olarak gönderir ve belli kurallara göre verilerimizi listeleriz. 

Belli kurallar dediğimiz deyimler CROSS  ve OUTER deyimleridir.

CROSS APPLY ile sadece fonkisyondaki değerlerle uyuşan satırlar bize geri döndürülmektedir. Bunu Join işlemi gibi düşünebiliriz. Fakat Join işlemiyle fonksiyonlar içerisine parametre gönderemezken APPLY operatörleriyle parametre gönderebilmekteyiz.

OUTER APPLY ise fonksiyondaki değerlere uyuşup uyuşmadığına bakmaksızın join edeceğimiz tablodaki değerleri bize geri döndürücekti. (Bunu bir nevi left join işlemi gibi düşünebiliriz)

Bir örnek vermek gerekirse;

Müşterilerimizin ve Siparişlerinin tutulduğu iki tablomuz olduğunu varsayalım. Biz dışarıdan girilecek müşteri id'sine göre müşterilerimizin siparişlerini listeleyen bir fonksiyon yazalım ve CROSS APPLY operatörüyle müşteri tablomuzu fonksiyonla birleştirelim.

CREATE FUNCTION  getorder

(

@custid as int, @n as int

)

RETURNS TABLE

AS 

RETURN

Select top (@n) * From Sales.SalesOrderHeader 

WHERE CustomerID=@custid

ORDER BY TotalDue Desc

GO

Select c.CustomerID,o.TotalDue from Sales.Customer as c

CROSS APPLY

getorder(c.CustomerID,5) as o ORDER BY CustomerID ASC,TotalDue DESC

Gördüğünüz gibi CROSS APPLY ile sadece fonksiyon içerisinde eşleşen CustomerID'lerin en yüksek 5 siparişini listelemiş olduk.

Eğer CROSS APPLY yerine OUTER APPLY  operatörünü kullansaydık fonksiyon içerisinde eşleşmeyen CustomerID'leri de listeleyecektik.

Konu ile ilgili Sunumum:

Writing Simple Case Expressions

Merhaba arkadaşlar,

Bugün sizlere Case Expressions hakkında çektiğim videoyu paylaşacağım. 

Bu videonun içeriğinde CASE yapısını, nasıl ve ne için kullanıldığını örneklerle anlatmaya çalıştım.

Görüşmek üzere, kendinize iyi bakın.