<\/div>\n

Bu yaz\u0131mda T-SQL de Constraint(K\u0131s\u0131tlay\u0131c\u0131) lardan bahsedece\u011fim.Constraintler sayesinde tablolar \u00fczerinde istedi\u011fimiz \u015fartlar ve durumlara g\u00f6re k\u0131s\u0131tlamalar yapabiliyoruz.
\nConstraintler be\u015f tanedirler.Bunlar,
\nCONSTRAINTLER (KISITLAYICILAR)<\/strong><\/span>
\n1. DEFAULT CONSTRAINT
\n2. CHECK CONSTRAINT
\n3. PRIMARY KEY CONSTRAINT
\n4. UNIQUE CONSTRAINT
\n5. FOREIGN KEY CONSTRAINT
\n1.DEFAULT CONSTRAINT<\/span><\/strong>
\nDefault constraint sayesinde kolona bir de\u011fer girilmedi\u011fi taktirde varsay\u0131lan olarak ne girilmesi gerekti\u011fini ayarlayabiliyoruz.
\nGenel yap\u0131s\u0131 \u015fu \u015fekildedir.
\nadd constraint \u2013isim- default \u2018varsay\u0131lan de\u011fer\u2019 for \u2013kolonad\u0131-
\n\u00d6ncelikle \u201cNorthwind\u201d veritaban\u0131nda \u00e7al\u0131\u015ft\u0131\u011f\u0131m\u0131z\u0131 ikaz ediyorum.\u201dCreate\u201d komutuyla \u201cdeneme\u201d ad\u0131nda bir tablo olu\u015ftural\u0131m ve ona default constraint olu\u015ftural\u0131m.<\/p>\n

\n
\r\ncreate table deneme\r\n(\r\nId int primary key identity(1,1),\r\nDenemea varchar(20),\r\nDenemeb varchar(20)\r\n)\r\n<\/pre>\n
\n
\u015eimdide bu tablodaki \u201cDenemea\u201d adl\u0131 kolona default constraint yazal\u0131m.Yani bu kolona de\u011fer girilmedi\u011fi zaman \u201cBo\u015f\u201d yazd\u0131ral\u0131m.<\/p>\n
\n
\r\nalter table deneme\r\nadd constraint dc_deneme default 'Bo\u015f' for Denemea\r\n<\/pre>\n
\n
\u201calter\u201d komutuyla \u201cdeneme\u201d adl\u0131 tabloda \u00e7al\u0131\u015fma yapaca\u011f\u0131m\u0131z\u0131 s\u00f6yledik.
\n\u201cdc_deneme\u201d ad\u0131nda \u201cdefault\u201d tipinde bir constraint olu\u015fturduk.\u201dDenemea\u201d kolonuna veri g\u00f6nderilmedi\u011fi zaman \u201cBo\u015f\u201d yaz\u0131lacakt\u0131r.<\/p>\n
\n
\r\ninsert deneme(Denemeb) values('deneme')\r\nselect * from deneme\r\n<\/pre>\n
\n
Yukarda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cDenemeb\u201d kolonuna veri g\u00f6nderiliyor ama \u201cDenemea\u201d kolonuna g\u00f6nderilmiyor.Select sorgusu \u00e7al\u0131\u015ft\u0131\u011f\u0131 zaman \u201cDenemeb\u201d kolonunda \u201cdeneme\u201d yaz\u0131s\u0131 g\u00f6r\u00fclecektir.\u201dDenemea\u201d kolonunda ise \u201cBo\u015f\u201d yaz\u0131s\u0131n\u0131 g\u00f6rece\u011fiz.<\/p>\n
\n
Bir ba\u015fka \u00f6rnek,<\/p>\n
\n
\r\nalter table deneme\r\nadd constraint dc_deneme default getdate() for Denemea\r\n<\/pre>\n
\n
Ad\u0131 \u201cdc_deneme\u201d olan \u201cdefault\u201d tipte olan constraint ekleniyor ve \u201cDenemea\u201d kolonu bo\u015f b\u0131rak\u0131ld\u0131\u011f\u0131nda \u201cgetdate()\u201d metoduyla o an\u0131n tarihi ekleniyor.
\n2.CHECK CONSTRA\u0130NT<\/span>
\nBir kolona girilecek olan verinin belirli bir \u015farta uymas\u0131n\u0131 zorunlu tutar.
\nGenel yap\u0131s\u0131 \u015fu \u015fekildedir,
\nAdd constraint -isim- check (\u015fart)
\n\u201cdeneme\u201d adl\u0131 tablomuz \u00fczerinden \u00f6rneklere devam edelim.<\/p>\n
\n
\r\nalter table deneme\r\nadd constraint cc_deneme check (Year(denemea)<1980)\r\n<\/pre>\n
\n
Ad\u0131 \u201ccc_deneme\u201d ve tipi \u201ccheck\u201d olan \u015fart\u0131da (Year(denemea)\u201cdenemea\u201d kolonuna y\u0131l\u0131 1980 y\u0131l\u0131ndan b\u00fcy\u00fck tarih giremeyiz.Girersek kay\u0131t i\u015flemi ger\u00e7ekle\u015fmeyecektir.\u015earta uyarsak ger\u00e7ekle\u015fecektir.
\nD\u0130KKAT!!
\n\u015eimdi ben \u201cdenemea\u201d kolonuma check constraint yazaca\u011f\u0131m.Ve \u015fart\u0131m \u015fu olacak.\u201ddenemea\u201d kolonuna 1990 y\u0131l\u0131ndan k\u00fc\u00e7\u00fck kay\u0131tlar\u0131 alma.Ancak \u015f\u00f6yle bir durum varki constraint i yazmadan \u00f6nce benim bu kolonumda 1990 y\u0131l\u0131ndan k\u00fc\u00e7\u00fck kay\u0131tlar\u0131m var varsayal\u0131m.\u0130\u015fte bu constrainti olu\u015fturmam\u0131za izin vermez.\u00c7\u00fcnk\u00fc \u201csenin tablonda zaten 1990 dan k\u00fc\u00e7\u00fck kay\u0131tlar var karde\u015fim\u201d diyecektir.<\/p>\n
\n
\r\nalter table deneme\r\nadd constraint cc_deneme check (YEAR(denemea)>1980)\r\nMsg 547, Level 16, State 0, Line 1\r\nThe ALTER TABLE statement conflicted with the CHECK constraint "cc_deneme". The conflict occurred in database "Northwind", table "dbo.deneme", column 'denemea'.\r\nHATA MESAJI BUNU VERECEKT\u0130R.\r\n<\/pre>\n
\n
Ancak ben istiyorum ki bundan sonra ki kay\u0131t i\u015flemlerinde bu istedi\u011fim \u015fart\u0131 ger\u00e7ekle\u015ftirmek istiyorum.\u0130\u015fte bu durumda add \u00f6n\u00fcne ekleyece\u011fimiz \u201cwith nocheck\u201d komutu imdad\u0131m\u0131za yeti\u015fecektir.
\nWith nocheck :<\/span> Kolonda \u015fu ana kadar yap\u0131lm\u0131\u015f kay\u0131tlar\u0131 g\u00f6rmezden gelip,bundan sonraki kay\u0131tlar\u0131 istedi\u011fimiz \u015farta g\u00f6re ayarlar.<\/p>\n
\n
\r\nalter table deneme\r\nwith nocheck add constraint cc_deneme check (YEAR(denemea)<1980)\r\n<\/pre>\n
\n
Art\u0131k 1980 y\u0131l\u0131ndan k\u00fc\u00e7\u00fck kay\u0131t yapmam\u0131za izin vermeyecektir.
\n3. PRIMARY KEY CONSTRAINT<\/span>
\nPrimary Key Constraint ile, o kolona eklenen primary key ile, ba\u015fka tablolarda foreign key olu\u015fturarak ili\u015fki kurmam\u0131z m\u00fcmk\u00fcn olur. Bunun yan\u0131nda o kolonun ta\u015f\u0131d\u0131\u011f\u0131 verinin tekil olaca\u011f\u0131 da garanti edilmi\u015f olur. Primary key constraint ile ayr\u0131ca CLUSTERED index olu\u015fturulmu\u015f da olur.
\nGenel kullan\u0131m\u0131 \u015fu \u015fekildedir,
\nadd constraint \u2013isim- primary key (-kolon ad\u0131-)
\nTabi bu constraintte kulland\u0131\u011f\u0131m\u0131z kolon identity \u00f6zelli\u011fine sahip olmal\u0131d\u0131r.<\/p>\n
\n
\r\nALTER TABLE DENEME\r\nADD CONSTRAINT PRMKC_DENEME PRIMARY KEY (ID)\r\n<\/pre>\n
\n
Ad\u0131 \u201cPRMKC_DENEME\u201d olan ve tipi \u201cPRIMARY KEY\u201d olan \u201cID\u201d kolonunda i\u015flem yapan constraint.
\n4. UNIQUE CONSTRAINT<\/span>
\nUnique constraintin tek amac\u0131,belirtti\u011fimiz kolondaki de\u011ferlerin tekil olmas\u0131n\u0131 sa\u011flamakt\u0131r.
\nGenel yap\u0131s\u0131 \u015fu \u015fekildedir,
\nadd constraint \u2013isim- unique (-kolon ad\u0131-)<\/p>\n
\n
\r\nALTER TABLE DENEME\r\nADD CONSTRAINT UC_DENEME UNIQUE (DENEMEA)\r\n<\/pre>\n
\n
\u201cDENEMEA\u201d kolonuna unique constraint verilerek tekil hale getirilmi\u015ftir.Bundan sonra ikitane ayn\u0131 veriden kay\u0131t yap\u0131lamamaktad\u0131r.
\n5. FOREIGN KEY CONSTRAINT<\/span>
\nTablolar\u0131n kolonlar\u0131 aras\u0131nda ili\u015fki kurmam\u0131z\u0131 sa\u011flar. Bu ili\u015fki neticesinde, primary key kolonu olan tablodan veri silinmesini, g\u00fcncellenmesini engeller.(foreign key olan kolondaki kar\u015f\u0131l\u0131\u011f\u0131n\u0131n bo\u015fa d\u00fc\u015fmemesi i\u00e7in)
\nGenel yap\u0131s\u0131 \u015fu \u015fekildedir,
\nAdd constraint \u2013isim- foreign key (-kolon ad\u0131-) references\u00a0 -2.tablo ad\u0131-(-2. Tablodaki kolon ad\u0131-)
\n\u015eimdi foreign key constrainti g\u00f6sterebilmek i\u00e7in iki tablo olu\u015ftural\u0131m.<\/p>\n
\n
\r\ncreate table deneme1\r\n(\r\nId1 int primary key identity(1,1),\r\nkolon1 nvarchar(50)\r\n)\r\ncreate table deneme2\r\n(\r\nId2 int primary key identity(1,1),\r\nkolon2 nvarchar(50)\r\n)\r\n<\/pre>\n
\n
\u201cdeneme1\u201d adl\u0131 tablodaki \u201cId1\u201d kolonunu foreign key yap\u0131p, \u201cdeneme2\u201d tablosundaki \u201cId2\u201d kolonuna ba\u011flayal\u0131m.<\/p>\n
\n
\r\nalter table deneme1\r\nadd constraint frc_deneme foreign key (Id1) references deneme2(Id2)\r\n<\/pre>\n
\n
\u201cdeneme1\u201d tablosundaki \u201cId1\u201d kolonunu foreign key yap.Bunu \u201cdeneme2\u201d tablosundaki \u201cId2\u201d kolonuna ba\u011fla.Bu constraintin ad\u0131 \u201cfrc_deneme\u201d olsun.
\nYukar\u0131daki i\u015flem yap\u0131ld\u0131ktan sonra \u201cdeneme2\u201d tablosundan \u201cdeneme1\u201d tablosunda kar\u015f\u0131l\u0131\u011f\u0131 olan bir veri silinmez ya da g\u00fcncellenemez.
\nPeki delete ve update durumlar\u0131nda ili\u015fkili kolondaki verilen davran\u0131\u015f\u0131 de\u011fi\u015ftirmek i\u00e7in \u201ccascade,set null,set default\u201d ayarlar\u0131 verilir.Bu ayarlar verilmedi\u011fi taktirde \u201cno action\u201d ge\u00e7erlidir.
\nCascade :<\/span> Ana tablodaki kay\u0131t silindi\u011finde ya da g\u00fcncellendi\u011finde , ili\u015fkili kolondaki kar\u015f\u0131l\u0131\u011f\u0131da otomatik olarak silinir ya da g\u00fcncellenir.
\nSet Null :<\/span> Ana tablodaki kay\u0131t silindi\u011finde ya da g\u00fcncellendi\u011finde , ili\u015fkili kolondaki kar\u015f\u0131l\u0131\u011f\u0131na \u201cNull\u201d de\u011fer bas\u0131l\u0131r.
\nSet Default :<\/span> Ana tablodaki kay\u0131t silindi\u011finde ya da g\u00fcncellendi\u011finde , ili\u015fkili kolondaki kar\u015f\u0131l\u0131\u011f\u0131na o kolonun default de\u011feri bas\u0131l\u0131r.Bu default de\u011fer dedi\u011fimiz default tipte bir constrainttir.Bunu kendimiz olu\u015fturabiliriz.<\/p>\n
\n
\r\nalter table deneme1\r\nadd constraint fccc_deneme foreign key (Id1)\r\nreferences deneme2(Id2)\r\non delete cascade\r\n<\/pre>\n
\n
Farkl\u0131 \u00f6rnek.<\/p>\n
\n
\r\nalter table deneme1\r\nadd constraint fccc_deneme2 foreign key (Id1)\r\nreferences deneme2(Id2)\r\non update cascade\r\n<\/pre>\n
\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Bu yaz\u0131mda T-SQL de Constraint(K\u0131s\u0131tlay\u0131c\u0131) lardan bahsedece\u011fim.Constraintler sayesinde tablolar \u00fczerinde istedi\u011fimiz \u015fartlar ve durumlara g\u00f6re k\u0131s\u0131tlamalar yapabiliyoruz.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[109,110,111,112,113,85,114],"class_list":["post-170","post","type-post","status-publish","format-standard","hentry","category-veritabani","tag-check","tag-constraint","tag-default","tag-foreign-key","tag-primary-key","tag-transact-sql","tag-unique"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=170"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/170\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}