Serdar Demir

SQL Server Veri Bütünlüğü Constraintler

Veri bütünlüğünü sağlama sorumluluğunu veritabanına taşıma veritabanı yönetimi açısından bir devrim sayılmaktadır.

Primary key: Boş değere izin vermez ve bir satırın değerini tekrar edilmesi engeller ancak boş bırakılabilir

Foreing Key:Bir sütüna ait değerin aynı veya diğer bir tabloda aynı olup olmadığını kontrol eder

Check Constraint: Aynı tablo içindeki bir sütun değeri ile diğer bir sutun değerini kontrol eder.

Nasıl oluşturulur?

constraint pk_stok primary key clustred(stokno),

constraint anahtar kelime  ve constrainte bir ad veriyoruz ve tablo ismi giriyoruz.

Oluşturulan constrainti görmek için aşağıdaki kodu query analayser a yazıyoruz

exec sp_help pk_stok —Sadece belirtilen constraint hakkında bilgi verir.

Ek bilgi:– kullanarak kodlarınıza açıklama ekleyebilirsiniz.

Microsot Sql Server 2000 üzerindeki kısıtlar, veri bütünlüğünü korumamızı sağlayacak kuralları tanımlamamızı sağlar. Kısıtlar, kolonlarda yer almasına izin vereceğimiz değerleri belirleyen kuralları tanımlamamızı ve  veri bütünlüğünü korumayı sağlayan standart mekanizmaları sağlar. Veri bütünlüğünü sağlayacak mekanizmalar arasında kısıtlar(constraints) trigger, stored procedure ve default’ lara gore daha çok tercih edilirler. Query Optimizer yüksek performanslı query execution plan oluşturabilmek için kısıt tanımlarını kullanır. Örneğin Unique kısıtı tanımlandığında belirtilen kolon için indeks oluşturulur. Oluşturulan bu indeksten özellikle aramalarda yararlanılır. Öncelikle veritabanı kısıt tiplerini ve ayrıntılarını inceleyeceğiz. Ortak hedef nedir? Veri bütünlüğünü(Data consistency) sağlamak.

2. Kısıt Tipleri

Çok çeşitli kısıt tanımları yapılabilmesine rağmen her bir kısıt aşağıdaki üç kategoriden birisine girer: ‘Entity’, ‘Domain’ ve ‘Refential Integrity’ kısıtları.

2.1 Alan (Domain) Kısıtları
Alan kısıtları bir veya birkaç kolon ile ilişkilidir. Temel amaç kolonun ya da birkaç kolonunun tanımlanan kritere uygun olmasıdır. Tabloya yeni satır eklendiğinde ya da var olan satırlardan birisi update edildiğinde kısıt, tablolardaki başka herhangi bir satırı dikkate almadan  gerekli kontrolü yapar. Kolon üzerinde taşınan dataya oadaklanılır. Bu tarz kısıtlara ‘Check Constraints’, ‘Default Constraints’, ‘Rules’ ve ‘Defaults’ kavramlarını incelediğimiz de daha ayrınıtılı bakacağız.

2.2 Varlık (Entity) Kısıtları
Varlık kısıtları satırlarla ilgilidir. Bu tarz kısıtlar tüm kolonu dikkate almak yerine özellkle tek bir satır ile ilişkilidir. Her bir satırın belli bir kolona ya da kolonlar topluluğuna göre unique olması , bu tarz kısıtlara verilebilecek en iyi örneklerden birisidir. Bu sayede bir satırın bir kolonundaki bilginin diğer hiç bir satırda bulunmaması garanti altına alınmış olur. Bu konuda daha ayrıntılı bilgiyi Primary key ve Unique kısıtlarını incelediğimizde göreceğiz.

2.3 Referansal – Bütünlük (Referential Integrity) Kısıtları
Referansal bütünlük kısıtları bir kolondaki bilginin diğer bir kolon ile eşleşmesi zorunluluğunu sağlayarak bütünlüğü korur. Eşleşen kolonlar farklı iki tablonun kolonları olabileceği gibi aynı tablonun kolonları da olabilir. Örneğin bir sipariş tablomuz olsun. Bu tablomuzdaki kolonlardan birisi de kredi kartı tipi olsun. Bizim uygulamamız için izin verdiğimiz kredi kartı firmaları;Visa, MasterCard, Discover ve American Express olsun. Referansal bütünlük kısıtları, adına alan tablosu(domain table) dediğimiz yapıları oluşturmamızı sağlar. Verdiğimiz örneğe göre uygulamamız için alan tablomuz kredi kartları tablosu olacak. Sipariş tablomuzdaki kredi kartı kolonunun bu tablodaki muhtemelen primary key kolonuyla eşleşmesi gerekecek. Dolayısıyla alan tabloları kabul edilecek değerleri içeren tablolarımız olarak da görülebilir. Bu konuda daha ayrıntılı bilgiyi foreign key kısıtlarını gördüğümüzde inceleyeceğiz.

3. Anahtar Kısıtları (Key Constraints)

Veritabanı tasarımı ve yönetiminin köşetaşlarından birisi de anahtarlardır. Hemen hemen tüm veritabanı şirketlerinin veritabanı yönetim sistemlerinde dört farklı anahtar kavramı vardır. Bunlar primary, foreign, alternate ve inversion anahtarlarıdır. İlk üçüne detaylı olarak bakacağız. Inversion anahtarları datayı sıralamak için kullanılan alternatif yollardan sadece birisidir.

3.1 Ana Alan (Primary Key) Kısıtları
Ana alanlar satırın unique(eşsiz – yegane) belirleyicileridir. Bu anahtarlar unique değerler taşımalıdır ve boş olamaz. İlişkisel veritabanlarındaki önemlerinden dolayı ana alan kısıtları diğer tüm anahtar ve kısıtların temelidir. Bir tablo sadece bir primary key’ e sahip olabilir. Primary key eşsizliği garanti altına aldığı gibi satırı belirten bir belirteç olarakta kullanılır. Primary key’ leri oluşturmanın iki yolu vardır: CREATE TABLE ve ALTER TABLE komutları. Primary key kolonunun küçük ve integer olması tavsiye edilir. Her bir tablonun bir primary key’si olması çok önemlidir.

Şimdi CREATE TABLE komutu içinde nasıl primary key tanımlandığını görelim:

USE Accounting
CREATE TABLE Employees
{
EmployeeID   int   IDENTITY   NOT NULL PRIMARY KEY,
EmployeeName   varchar(30)   NOT NULL,
SSN   varchar(11)   NOTNULL,
Address1   varchar(25)   NOT NULL,
Address2   varchar(20)   NOT NULL,
City   varchar(20)   NOTNULL,
State   varchar(2)   NOT NULL,
Zip   varchar(10)   NOT NULL,
Contact   varchar(25)   NOT NULL,
Phone   char(15)   NOT NULL,
Salary   money   NOT NULL,
HireDate   smalldatetime   NOT NULL,
TerminationDate   smalldatetime   NULL,
EmpManagerID   int   NOT NULL
Department   varchar(25)   NOT NULL
}

Var olan bir tablo üzerinde primary key tanımlamak da oldukça kolaydır.

Use Accounting
ALTER TABLE Customers
ADD CONSTRAINT pk_custID PRIMARY KEY (CustomerID)

Birden fazla kolonda satırı tek başına unique bir şekilde tanımlayabilir. Bu şekilde satırı unique bir şekilde tanımlayan birden fazla kolondan oluşan kombinasyon sayısı da birden fazla olabilir. Bu durumda her bir kombinasyona aday anahtar denir. Bu durumda primary key’ yi oluşturabilmek için bu aday anahtarlardan birisi seçilir. Örneğin Araba_Parcaları tablosunda hem parca numarası hem de parca ismi aday anahtarlardır, ancak sadece parca numarası primary key olarak secilmiş olabilir.

CREATE TABLE Car_Parts
(
Part_No   int   PRIMARY KEY,
Part_Name  char(30),
Part_Weight  decimal(6,2),
Part_Color   char(15)
)

Primary key constraint tarafından oluşturulan indeks, non-clustered indesklerin sayısının 249’ u, clustered indekslerin sayısının da 1 ‘ i geçmemesine neden olmaz. Eğer primary key constraint tanımlanırken CLUSTERED ya da NONCLUSTERED denmemişse ve başka hiç bir clustered indeks kullanılmamışsa(unique constraint’ ler icin) oluşturulan primary key clustered olur.

3.1 Foreign Key Kısıtları
Foreign key tablolar arasındaki veri bütünlüğünü sağladığı gibi ilişkili tabloları da gösterir. Bir tabloya foreign key eklediğimizde referans edilen ve referans eden iki tablo arasında bir ilişki kurulur. Bir tablo için foreign key ayarlanmışsa bu tabloya girilecek her kaydın referans edilen tablodaki kayıtla eşleşmesi gerekir. Eğer foreign key olan kolon null değerler alabiliyorsa eşleşmeye bakılmaz. Birkaç örnekle konu daha iyi anlaşılacaktır.

Foreign key tanımlama sentaksı aşağıdaki gibidir.

FOREIGN KEY
REFERENCES <table name> (<column name>)
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]

USE Accounting
CREATE TABLE Orders
(
OrderID   int IDENTITY   NOT NULL PRIMARY KEY
CustomerID  FOREIGN KEY
REFERENCES Customers (CustomerID),
OrderDate   smalldatetime   NOT NULL,
EmployeeID   int   NOT NULL
)

Referans edilen kolonun ya primary key ya da üzerinde unique constraint tanımlanmış olması gerektiğini unutmayın. Unique kısıtlarını az sonra inceleyeceğiz. Yukarıda yazdığımız kodun başarılı bir şekilde gerçekleşip gerçekleşmediğinden emin olmak için aşağıdaki komutu kullanabilirisiniz.

Bu komutu çalıştırdığımızda tablodaki tüm kısıtlar ile ilgili olarak isimlerini, kriterlerini ve durumları hakkında bilgi alırız. Order tablomuz için bir adet primary key bir adet de foreign key olduğunu göreceğiz. CLUSTERED kelimesi primary key’ den hemen sonra geldiğini göreceğiz. Bu belirtilen indeksin clustered yapıda olduğunu gösterir. Bir tabloda birden fazla sayıda foreign key olabilir, ancak bir adet primary key olabilir. Bir tabloda en fazla 253 adet foreign key tanımlanabilir. Bu konuda tek sınırlandırma bir kolon sadece bir adet foreign key’ i referans edebilir.

Var olan tablolara yeni bir foreign key eklemek de oldukça basittir.

4. Kendi Kendini Referans Eden Tablo Oluşturma

Eğer referans edilen kolon ile referans eden kolon aynı tabloda ise ne olacak? Problem değil. Bir tablo aynı anda hem referans edilen hem de referans eden konumunda olabilir. Böyle bir tablo oluşturulurken bu kısıtı uygulamadan önce en az bir kayıt bulunması gerekir. Çünkü eklenmeye çalışılan satırın referans eden kolonunun gösterdiği referans edilen satır ortada yoktur. Aşağıadaki örneği inceleyelim.

Use Accounting
ALTER TABLE Employees
ADD CONSTRAINT Fk_EmpManager
FOREIGN KEY (EmpMangerID) REFERENCES Employees (EmployeeID)

USE Accounting
CREATE TABLE Employees
{
EmployeeID   int   IDENTITY   NOT NULL PRIMARY KEY,
EmployeeName   varchar(30)   NOT NULL,
SSN   varchar(11)   NOT NULL,
Address1   varchar(25)   NOT NULL,
Address2   varchar(20)   NOT NULL,
City   varchar(20)   NOTNULL,
State   varchar(2)   NOT NULL,
Zip   varchar(10)   NOT NULL,
Contact   varchar(25)   NOT NULL,
Phone   char(15)   NOT NULL,
Salary   money   NOT NULL,
HireDate   smalldatetime   NOT NULL,
TerminationDate   smalldatetime   NULL,
EmpManagerID   int   NOTNULL
REFERENCES EMPLOYEES(EmployeeID),
Department   varchar(25)   NOT NULL
}

Eğer employee tablosunu silmeye çalışırsak error mesajı alırız. Order tablosu ile Employee tablosu arasında bir referans kurduğumuz için bu referansı kaldırmadan employee tablosunu silemeyiz.

5. Otomatik(Cascading) Güncellemeler ve Silmeler

Otomatik silme ve güncellemeler “Cascading” olarak bilinmektedir. İçerisinde karşılığı olmayan bir foreign key (Null dışında) bulunduran bir satırı insert edemeyiz. ON DELETE cümleciği içerisinde referans edilen bir kolon bulunan satırın silinmeye çalışılması sırasında nasıl davranılacağını belirtmek için kullanılır. 2 opsiyonludur.

1. NO ACTION – Bu durumda silme işlemi başarısızlıkla sonuçlanır ve error mesajı alınır.

2. CASCADE – Bu durumda kendisine referans veren tüm satırlar ile birlikte silme işlemi gerçekleşir.

ON UPDATE cümleciği bir foreign key tarafından referans edilen bir kolonun update olması sırasında nasıl bir davranışta bulunulması gerektiğini ayarlamak için kullanılır. Aynı şekilde “No action” ve “Cascade” opsiyonları vardır.

Aşağıdaki kod bu kavramları daha iyi açıklamaktadır.

CREATE TABLE Order_Parts
(Order_NO int,
Part_NO   int;
FOREIGN KEY REFERENCES Car_Parts(Part_NO)
ON DELETE NO ACTION,
qty_ordered int)
GO

Order_Parts tablosu ile Car_Parts tablosu arasında bir ilişki(Foreign key) tanımlanmıştır. Genellikle Order_Parts tablosu ile Orders tablosu arasında da bir ilişki olur. Ancak örneğin bastiliği açısından bunu gerçekleştirmeyelim.

Foreign key kolonuna null dışında bir değer girildiğinde bunun mutlaka diğer kolonda bir karşılığı olmalıdır. Foreign key kısıtları sadece aynı server üzerindeki aynı varitabanı üzerinde bir kolona referans verebilir. Diğer durumlar için cross-database referential integrity, trigger mekanizması yardımıyla kullanılmalıdır. Foreign key’ ler aynı tablodaki başka bir kolonu da referans edebilir(self-reference).  References cümleciği ile sadece bir kolona referans verilebilir. Bir kolon birden fazla kolona referans veremez. Ve referans verilen kolon da aynı veri tipinde olmalıdır. Table seviyesinde ise referans veren kolon sayısı ile referans edilen kolon sayısı aynı olmalıdır. Referans eden ve edilen kolonlar aynı veri tipine haiz olmalıdır. Referans edilen ve eden kolonlar timestamp veritipinde ise CASCADE cümleciği kullanılmamalıdır. Referans edilen ve eden tablolar arasında CASCADE ve NO ACTION opsiyonlarına gore ilişki kurulabilir. Eğer Sql Server No action ile karşılaşırsa işlemi iptal ederek error mesajı döndürür. Bu arada daha önceden yapılmış cascade işlemleri varsa onlar da rollback olur. Silme işlemleri biraz farklıdır. Eğer bir silme işlemi sırasında cascade ve no action’ lar varsa öncelikle cascade işlemleri gerçekleştirilir. Foreign key kısıtları temporarily(geçici) olarak oluşturulmuş tablolar üzerinde gerçeklenemez. Referans edilen kolon ya primary key ya unique kısıtı ya da unique indeksi içinde yer almak zorundadır.

6. Unique Kısıtlayıcılar

En kolay yönetilebilen ve primary key’ lere benzeyen bir kısıttır. Belirtilen kolonun ya da kolonlar toplulugunun taşıdığı değerlerin unique olmasini saglar. Sıklıkla bu kısıtlara alternatif anahtarlar da(alternate keys) denir. Alternatif anahtarlar tablodaki satırı tanımlayan unique bir belirteç olarak düşünülmez. Unique kısıtlar birden fazla olabilir. Bir unique kısıt uygulandığında ilgili kolondaki tüm değerlerin unique olması gerekir. Tersi durumlarda SQL Server bir error mesajı döndürür.

Primary key’ lerdeki durumun tersine unique key kolonlarında Null değerler girilebilir. İstemiyorsak özellikle belirtmeliyiz. Ancak null değerlere izin versek bile unique’ liğin bozulmaması açısından en fazla bir adet null değer barındırılabilir. Normalde bir null başka bir null’ a eşit değildir.

Şimdi Accounting veritabanında shipment adında bir tablo oluşturalım.

USE Accounting
CREATE TABLE Shipment
(
ShipperID   int   IDENTITY   NOT NULL   PRIMARY KEY,
ShipperName   varchar(30)   NOT NULL,
Address   varchar(25)   NOT NULL,
City   varchar(25)   NOT NULL,
State   varchar(2)   NOT NULL,
Zip   varchar(10)   NOT NULL,
Phone   varchar(14)   NOT NULL UNIQUE
)

Benzer şekilde var olan bir tabloya  unique kısıtlayıcı eklemek de oldukça basittir.

Use Accounting
ALTER TABLE Employees
ADD CONSTRAINT Ak_EmpSSN UNIQUE (SSN)

7. Kontrol (Check) Kısıtları

Kontrol kısıtları ile ilgili en güzel şey belirli bir kolona bağlı olmamaktır.  Kontrol kısıtlarını belirli bir kolona da uygulayabiliriz. Ancak kontrol kısıtları önemini daha çok  tablo – yönelimli(table-oriented) yapısından almaktadır. Bir kolondaki değerin geçerliliğini başka bir kolonla karşılaştırarak elde edebilmemize olanak sağlar. Bu noktada önemli olan aynı satırın kolonları olmasıdır. Kontrol kısıtlarının belli başlı tipik kullanımları ile ilgili örnekler aşağıda gösterilmiştir.

1. Ay kolonunu sınırlandırmak – SQL komutunda “Between 1 and 12”
2. Amerikan Sosyal  Güvenlik Numarası formatı – SQL komutunda [0-9][ 0-9][ 0-9] – [0-9][ 0-9][ 0-9]-[ 0-9][ 0-9][ 0-9][ 0-9]
3. Şehir kolonu için girilen şehrin geçerliliğini kontrol etme, CHECK(City) IN (“NY”, “FL”, “CA”, “MA”)

Aşağıdaki örnekte min_lvl kolonunun 10’a eşit ve büyük olması, max_lvl kolonunun da 250’ ye eşit ya da küçük olması kontrolleri için gerekli kontrol kısıtları uygulanmıştır.

CREATE TABLE Jobs
(
job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
job_desc varchar(50)NOT NULL,
min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)
)

Bu şekilde min_lvl kolonuna 10’ dan küçük max_lvl kolonuna da 250’ den büyük değerler girildiğinde error alınacaktır.

7.1 Var olan bir tablo üzerinde yeni kontrol kısıtı ekleme

Customer isimli tablomuzun DateInSystem isimli kolonunun değeri satır ekleme ya da güncelleme sırasında sistemdeki tarihtir. Şimdi bu kolona bir kontrol kısıtı ekleyelim. Bu kolona işlem yapılırken sistem tarihinden daha büyük bir tarihin girilmesini engelleyelim.

Use Accounting
ALTER TABLE Customers
ADD CONSTRAINT Chk_DateSys CHECK (DateInSystem <= GETDATE())

Bir kolon için birden fazla sayıda kontrol kısıtı tanımlanabilir, hatta durumlar or veya and’ lenebilir. Bir kolon için birden fazla kontrol kısıtı tanımlanmışsa bunların kontrol edilme sırası oluşturulma sıraları ile aynıdır. Kontrol kısıtları içerisinde başka tabloları kullanamayız ve mutlaka boolean bir sonuç dönmelidir. Kolon seviyesindeki kontrol kısıtları sadece kolona referans verebilirken, tablo seviyesindeki kontrol kısıtları tablo içerisindeki her kolona referans verebilir. Kontrol kısıtları ve kurallar(rule) aynı fonksiyonelliğe(Satır ekleme ve silme de girilen bilginin doğruluğunu kontrol etme) sahiptir. Bir kolon için birden fazla kontrol kısıtı ve bu kolonu da ilgilendiren kural(rule) tanımı yapılmışsa bütün hepsi değerlendirilir.

8. Varsayılan (Default) Kısıtlar

Diğer tüm kısıtlar gibi, varsayılan kısıtlar da tablo tanımının bir parçası olmuş durumdadır. Bir kolona varsayılan kısıt eklendiğinde, satır ekleme ve güncellemeler de bu kolonun boş bırakılması durumunda nasıl davranılacağını tanımlanmış olan varsayılan kısıt belirler. Varsayılan değer olarak string bir ifade(3000$) girilebileceği gibi çeşitli sistem tanımlı değerler de (GETDATE()) girilebilir.

Aşağıdaki örneği inceleyelim.

USE Accounting
CREATE TABLE Shippers
(
ShipperID  int  IDENTITY NOT NULL PRIMARY KEY,
ShipperName  varchar(30)  NOT NULL,
Address  varchar(25)  NOT NULL,
DateInSystem  smalldatetime  NOT NULL DEFAULT GETDATE()
)

Oluşturulan bu tabloya şöyle bir ekleme yapılıyor.

Insert into Shippers (shipperName) Values (‘UPS’)

Select * from Shippers;

Yukarıdaki script ile oluşturulan tablo da emp_id kolonu primary key(birincil anahtar) konumundadır. Emp_Id kolonundaki verinin de şu şartları sağlamasını istedik. İlk üç ya da iki hane kişinin ismi ve soyisminin başharfleri ondan sonra gelen üç hane ise 100-999 arasında bir sayıyı destekleyecek şekilde olacaktır. Son hane ise cinsiyet hanesi olup M ya da F içerecektir. Job_Id kolonu Jobs tablosunun Job_Id kolonuna referans veriyor. Bu yüzden bir foreign key kısıtı eklendi. Job_lvl kolonuna varsayılan kısıt eklenmiş ve değer 10 yapılmıştır. Bu kolona veri girilmediği takdirde 10 değeri varsayılan bir değer olarak eklenecektir. Benzer şekilde sistem tarihi de varsayılan kısıt değeri olarak hire_date kolonuna eklenmiştir.

Özetle şimdiye kadar SQL Server 2000’ deki kısıt tiplerini, alt sınıflarını, spesifik kısıtlar ile tablo oluşturmayı, var olan tablolarda kısıtlar ile ilgili modifikasyonları inceledik. Artık bundan sonrası size kalmış.Kendinizi bu konuda örnek kodlar yazarak ustalaştırın.