Anasayfa / Yazılım / SQL / Foreign Keys Oluşturma

Foreign Keys Oluşturma

Foreign keys özellikle birbiri ile bağlantılı olan iki tablo içinde primary key’e sahip olan tablo ile bağlantı kurabilmek için kullanılır. Yani şöyle diyelim elinizde iki tablo var ve bu tablolar arasında ilişki kurmak istiyorsunuz, A tablosunda primary key olarak MusteriID kolonumuz olsun. Burada Müşteriye ait olan ad, soyad, cinsiyet, yaş gibi bilgiler mevcut. Diğer tablomuzda da müşteriye ait diğer ek bilgiler mevcut. Bu iki tablo arasında MusteriID üzerinden bir ilişki kurulur. İşte ikinci tablomuzda MusteriID foreign keys olarak tanımlanır. Tabi iki tablo arasında ilişki kurabilmek için illa ki foreign keys kullanmaya gerek yoktur. Ama performans açısından foreign keys tanımlamak faydalı olacaktır. Böylelikle veriler arasındaki tutarlılık da rahatlıkla takip edilebilecektir.  Şimdi kod olarak foreign keys nasıl tanımlanır değinelim. CREATE TABLE komutu ile birlikte tıpkı primary keys tanımlar gibi foreign key de tanımlayabiliriz. Veya mevcut bir tablo üzerinde ALTER TABLE diyerek de değişiklik yapabiliriz. Foreign key tanımlama için syntax aşağıdaki gibidir:

CREATE TABLE ( FOREIGN KEY ())

İki kolon için örnek syntax:

CREATE TABLE ( , ,
CONSTRAINT FOREIGN KEY (,)
REFERENCES (,))

Değişiklik yaparken syntax:

CREATE TABLE ( , )
ALTER TABLE ADD CONSTRAINT FOREIGN KEY ()
REFERENCES ())

Örnek olarak kullanımı SQL kodlarında görelim:

CREATE TABLE Tablo1 (kolon1 INT NOT NULL PRIMARY KEY,
kolon2 VARCHAR(20), kolon3 DATETIME);

CREATE TABLE tablo2 (kolon4 INT NULL,
kolon5 VARCHAR(20) NOT NULL,
CONSTRAINT pk_tablo2 PRIMARY KEY (kolon5),
CONSTRAINT fk_tablo2_tablo1 FOREIGN KEY (kolon4) REFERENCES tablo1(kolon1)
);
GO

PRINT 'Tablo 1 e ekleme işlemi';
INSERT INTO tablo1(kolon1,kolon2,kolon3)
VALUES(1,'a','1/1/2015'),(2,'b','1/2/2015'),(3,'c','1/3/2015');

PRINT 'Tablo 2 ye ekleme işlemi';
INSERT INTO tablo2(kolon4,kolon5)
VALUES(1,'abc'),(2,'def');

INSERT INTO tablo2(kolon4,kolon5)
VALUES (7,'abc');


UPDATE tablo2 SET kolon4 = 6
WHERE kolon4 = 1;

Öncelikle tablo1 ‘i oluşturduk, primary keye sahip olduğunu belirttik. Daha sonra tablo2’yi oluşturduk ve tablo1 ve tablo2’de foreign key tanımladık.  Daha sonra tablo1’e 3 satır ekledik. Daha sonra tablo2’te 2 satır veri ekledik.

Delete ve Update işlemleri ile birlikte Foreign Keys Kullanımı:

Şimdi iki ayrı tbaloda bağlantı yani ilişki kurabildiğimize değinmiştik. Örneğin iki adet tablomuz olsun, birisi müşteriler, diğeri ise siparişler tablosu olsun. Müşteriler tablosunda yer alan MusteriID ile siparişler tablosunda yer alan MusteriID birbiri ile ilişkili olsun. Musteri tablosunda MusteriID primary key, Siparisler tablsoundaki MusteriID ise foreign key olsun. Şimdi siparişler tablosundaki bir sipariş silindiğinde yani bir müşteriye ait sipariş silindiğinde o müşterinin diğer siparişleri silinecek mi? Müşterinin kaydı silinecek mi? MusteriID bilgileri değişecek mi? Bu gibi değişimlerin olup olmamasını yönetmek amacı ile foreign key tanımlarken ilgili ayarları yapabiliriz. Bu ayarlamalar için kullanılan tanımlamalar şöyledir:

CASCADE: Foreign key içeren tabloya aynı aksiyonu uygular.

NO ACTION: Silme ve / veya güncelleme işlemini geri alır. Aksiyon alınmaz.

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

SET DEFAULT: Foreign key kolonlarını varsayılan değerlerine atar.

Aşağıdaki UPDATE ve DELETE işlemleri için foreign key kullanımına dair syntax mevcuttur:

CREATE TABLE ( , ,
CONSTRAINT FOREIGN KEY () REFERENCES ()
[ON DELETE [NO ACTION|CASCADE|SET NULL|SET DEFAULT]] [ON UPDATE [NO ACTION|CASCADE|SET NULL|SET DEFAULT]])

Şimdi örnekler ile konuyu anlatalım.

CREATE TABLE tablo1 (kolon1 INT NOT NULL PRIMARY KEY,
kolon2 VARCHAR(20), kolon3 DATETIME);

Tablo1 adında 3 sütunlu bir tablo oluşturuyorum. 1. sütun Int tipinde ve primary key. İkinci sütun Varchar tipinde, 3. sütun ise datetime tipinde.

CREATE TABLE tablo2 (kolon4 INT NULL DEFAULT 7,
kolon5 VARCHAR(20) NOT NULL,
CONSTRAINT pk_tablo2 PRIMARY KEY (kolon5),
CONSTRAINT fk_tablo2_tablo1 FOREIGN KEY (kolon4) REFERENCES tablo1(kolon1)
);

Tablo2 adında bir tablo oluşturuyorum. Tablo1’i referans alarak foreign ley kullanarak, NO ACTION kuralı dahilinde. NO ACTION ile tablo üzerinde güncelleme ve silme işlemlerini engelliyorum.

INSERT INTO tablo1(kolon1,kolon2,kolon3)
VALUES(1,'a','1/1/2009'),(2,'b','1/2/2009'),(3,'c','1/3/2009'),
(4,'d','1/4/2009'),(5,'e','1/6/2009'),(6,'g','1/7/2009'),
(7,'g','1/8/2009');

Tablo1′ e veri ekliyorum.

INSERT INTO tablo2(kolon4,kolon5)
VALUES(1,'abc'),(2,'def'),(3,'ghi'),
(4,'jkl');

Tablo2’ye veri ekliyorum.

SELECT kolon4, kolon5 FROM tablo2;

Verilerin eklendiğini görelim.

20150921_06

DELETE FROM tablo1 WHERE kolon1 = 1;

Şimdi veri silmeye çalışıyorum. Sonuç itibari ile hata alacağım. Alacağım hata aşağıdkai şekilde.

“The DELETE statement conflicted with the REFERENCE constraint “fk_tablo2_tablo1”. The conflict occurred in database “deneme”, table “dbo.tablo2”, column ‘kolon4’.
The statement has been terminated.”

ALTER TABLE tablo2 DROP CONSTRAINT fk_tablo2_tablo1;

Tablo2 üzerindeki constrainti kaldırıyorum.

ALTER TABLE tablo2 ADD CONSTRAINT fk_tablo2_tablo1
FOREIGN KEY (kolon4) REFERENCES tablo1(kolon1)
ON DELETE CASCADE
ON UPDATE CASCADE;

Yine foreign key oluşturuyorum ama bu sefer CASCADE yapısı ile oluşturulmasını sağlıyorum.

DELETE FROM tablo1 WHERE kolon1 = 1;

Tablo1’den kolon1 değeri 1 olan veriyi siliyorum. CASCADE yapısına sahip olması nedeni ile aynı zamanda otomatik olarak tablo2’de yer alan yine eşleşen diğer satırda silinir.

UPDATE tablo1 SET kolon1 = 10 WHERE kolon1 = 4;

Tablo1 üzerinde kolon1 değeri 4 iken bu değeri yani kolon1 değerini 10 yapıyorum. CASCADE yapısına sahip olması nedeni ile aynı zamanda otomatik olarak tablo2’de yer alan yine eşleşen diğer satırda güncellenir.

ALTER TABLE tablo2 DROP CONSTRAINT fk_tablo2_tablo1;

Tablo2 üzerindeki CASCADE constraintini kaldırıyorum.

ALTER TABLE tablo2 ADD CONSTRAINT fk_tablo2_tablo1
FOREIGN KEY (kolon4) REFERENCES tablo1(kolon1)
ON DELETE SET NULL
ON UPDATE SET NULL;

Tablo2 üzerinde bu sefer SET NULL ayarlarını yaparak yeni bir constraint ekliyorum.

DELETE FROM tablo1 WHERE kolon1 = 2;

Tablo1 üzerinde kolon1 değeri 2 olan satırı siliyorum. Aynı şekilde tablo2 üzerinde eşleşen satırda otomatik olarak silinir.

ALTER TABLE tablo2 DROP CONSTRAINT fk_tablo2_tablo1;

Tablo2 üzerinde yer alan SET NULL constrainti kaldırıyorum.

ALTER TABLE tablo2 ADD CONSTRAINT fk_tablo2_tablo1
FOREIGN KEY (kolon4) REFERENCES tablo1(kolon1)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT;

Tablo2 üzerinde bu sefer foreign key olarak SET DEFAULT yapısını tanımlıyorum.

DELETE FROM tablo1 WHERE kolon1 = 3;

Kolon1 değeri 3 olan satırı Tablo1 üzerinden siliyorum. Otomatik olarak Tablo2 üzerinde de ilgili değişiklik yapılır.

SELECT kolon4, kolon5 FROM tablo2;

Tablo2 üzerinde kolon4 ve kolon5 verileri listeliyorum.

20150921_07

 

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir