SQL'de Bütünlük Kısıtları: Örneklerle Rehber

Alerjileri boş bırakılamayan bir hasta veritabanını veya işlem tutarlarının pozitif olmak zorunda olduğu bir finansal sistemi hayal edin. Bu ve benzeri sayısız senaryoda, verilerimizin doğru, tutarlı ve güvenilir kalmasını sağlamak için bütünlük kısıtlarına güveniriz. 

SQL'de bütünlük kısıtları, veri kalitesini korumak için veritabanı tablolarımıza uyguladığımız kurallardır. Hataları önlemeye, iş kurallarını dayatmaya ve verilerimizin temsil ettiği gerçek dünya varlıklarını ve ilişkilerini doğru yansıtmasına yardımcı olurlar.

Bu yazıda, SQL'deki temel bütünlük kısıdı türlerine değinecek, PostgreSQL veritabanında kullanımını göstermek için net açıklamalar ve pratik örnekler sunacağız. PostgreSQL sözdizimini kullanacağız, ancak kavramlar diğer SQL lehçelerine de kolayca uyarlanabilir.

SQL hakkında daha fazla bilgi edinmek istiyorsanız, şu SQL kursları listesini inceleyin.

SQL'de Bütünlük Kısıtları Nedir?

Bir web uygulaması için kullanıcı bilgilerini saklayan bir tabloya sahip olduğumuz bir senaryoyu düşünün. Kullanıcının yaşı gibi bazı veriler, uygulamamıza erişimi engellemediği için isteğe bağlı olabilir. Ancak, her kullanıcı için giriş amacıyla bir parolanın bulunması zorunludur. Bunu sağlamak için, kullanıcılar tablomuzun parola sütununa bir bütünlük kısıtı uygular ve bu tabloda her kaydın bir parola içerdiğini garanti ederiz.

Özetle, bütünlük kısıtları şunlar için kritiktir:

• Eksik verileri önleme.
• Tüm verilerin beklenen tür ve değer aralıklarına uymasını sağlama.
• Farklı tablolar arasındaki veriler arasında doğru bağlantılar kurma.

Bu yazıda, SQL'deki aşağıdaki temel bütünlük kısıtlarını inceleyeceğiz:

• PRIMARY KEY: Tablodaki her kaydı benzersiz olarak tanımlar.
• NOT NULL: Bir sütunun NULL değer içerememesini sağlar.
• UNIQUE: Bir sütun veya sütun grubundaki tüm değerlerin benzersiz olmasını sağlar.
• DEFAULT: Belirtilmediğinde bir sütun için varsayılan bir değer sağlar.
• CHECK: Bir sütundaki tüm değerlerin belirli bir koşulu sağlamasını güvenceye alır.
• FOREIGN KEY: Başka bir tabloda bir birincil anahtara referans vererek tablolar arasında ilişkiler kurar.

Vaka Çalışması: Üniversite Veritabanı

Bir üniversite için ilişkisel bir veritabanı ele alalım. Bu veritabanı üç tablo içerir: students, courses ve enrollments.

Students tablosu

students tablosu, üniversitedeki tüm öğrencilerle ilgili bilgileri tutar.

• student_id: Öğrencinin tanımlayıcısı
• first_name: Öğrencinin adı.
• last_name: Öğrencinin soyadı.
• email: Öğrencinin e-posta adresi.
• major: Öğrencinin ana dalı.
• enrollment_year: Öğrencinin kayıt olduğu yıl.

Courses tablosu

courses tablosu, üniversitede mevcut olan derslerle ilgili bilgileri tutar.

• course_id: Ders tanımlayıcısı.
• course_name: Dersin adı.
• department: Dersin bağlı olduğu bölüm.

Enrollments tablosu

enrollments tablosu, hangi öğrencinin hangi derslere kayıtlı olduğunu saklar.

• student_id: Derse kayıtlı öğrencinin tanımlayıcısı.
• course_id: Dersin tanımlayıcısı.
• year: Kayıt yılı.
• grade: Öğrencinin bu dersteki notu.
• is_passing_grade: Notun geçer olup olmadığını belirten bir boolean.

Yazı boyunca bu örnek veritabanını kullanacak ve veri bütünlüğünü sağlamanın çeşitli yollarını göstereceğiz. Sorgularımızda PostgreSQL sözdizimini kullanacağız. Ancak kavramlar diğer SQL türlerine de kolayca uyarlanabilir.

PRIMARY KEY Kısıtı

Üniversite her öğrenciyi benzersiz olarak tanımlamak ister. Bu amaçla ad ve soyad kullanmak, öğrenciler arasında aynı adın bulunma olasılığı nedeniyle uygun değildir. Benzer şekilde, e-posta adreslerine güvenmek de ideal değildir çünkü öğrenciler e-postalarını değiştirebilir.

Benzersiz tanımlamayı sağlamak için yaygın çözüm, her öğrenciye benzersiz bir tanımlayıcı atamak ve bunu student_id sütununda saklamaktır. Her öğrencinin benzersiz bir tanımlayıcıya sahip olmasını güvenceye almak için student_id sütununa bir PRIMARY KEY kısıtı uygulayabiliriz.

Bu kısıt, sütun veri türünün belirtilmesini takiben CREATE TABLE komutunda tanımlanır:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);

Yukarıdaki sorgu, belirttiğimiz altı sütunla öğrenci tablosunu oluşturur.

PRIMARY KEY kısıtı şunları sağlar:

1. Her öğrencinin bir student_id'si vardır.
2. Her student_id benzersizdir.

Birden fazla sütunda PRIMARY KEY kısıtı

Bazı durumlarda, her satırı benzersiz olarak tanımlamak için birden fazla sütun kullanmamız gerekir. Örneğin, enrollments tablosunu ele alalım. Bir öğrenci birden çok derse kayıt olabilir; bu da aynı student_id'yi paylaşan birkaç satıra yol açar. Benzer şekilde, bir derse birden çok öğrenci kayıt olabilir; bu da aynı course_id'ye sahip birçok satır oluşmasına neden olur.

Tek bir alanın satırı benzersiz tanımlayamadığı göz önünde bulundurulduğunda, her kayıt, student_id, course_id ve year kombinasyonu ile belirlenir.

Birden fazla sütun söz konusu olduğunda, PRIMARY KEY kısıtı CREATE TABLE komutunun sonunda belirtilir.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);

Tablo oluşturulduktan sonra bütünlük kısıtları

Bütünlük kısıtları eklemenin iki yolu vardır. Az önce bunu tablonun oluşturulması sırasında nasıl yapacağımızı öğrendik.

Tablonun zaten var olduğunu ve PRIMARY KEY kısıtını belirtmeyi unuttuğunuzu varsayalım. Bunu, tablo oluşturulduktan sonra ALTER TABLE komutunu kullanarak da tanımlayabilirsiniz:

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);

ALTER TABLE sorgusunda kısıta enroll_pk (kayıt birincil anahtarı) adını verdik. Bu ad, seçtiğiniz herhangi bir tanımlayıcı olabilir; ancak kısıtın amacını özlü bir şekilde ifade eden bir ad seçmeniz önerilir.

Bütünlük kısıtlarına ad vermek en iyi uygulamadır; çünkü birkaç fayda sağlar:

• Özellikle gelecekte kısıtı değiştirmeniz veya kaldırmanız gerektiğinde başvurmayı kolaylaştırır.
• Özellikle çok sayıda kısıta sahip veritabanlarında kısıtların yönetimini ve düzenini destekler.

NOT NULL Kısıtı

Üniversite, her öğrencinin adının ve e-postasının veritabanında kayıtlı olmasını sağlamak istiyor. Personelin bu alanları yanlışlıkla boş bırakmasını istemiyorlar.

Bunu aşmak için, tabloyu oluştururken bu üç sütunda NOT NULL kısıtlarını kullanabiliriz:

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);

Yukarıdaki sorgu, first_name, last_name ve email sütunlarının NULL (tanımsız) değer alamamasını sağlamak için NOT NULL kısıtlarını kullanır.

Var olan bir tabloya NOT NULL kısıtı eklemek için ALTER TABLE komutu kullanılabilir. NOT NULL kısıtlarını ekleme sözdizimi şöyledir:

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;

UNIQUE Kısıtı

E-posta adresleri doğası gereği her birey için benzersizdir. Bir alanın asla yinelenen değerlere sahip olmaması gerektiğinden emin olduğumuz durumlarda, bunu veritabanı düzeyinde dayatmak iyi bir uygulamadır. Bu, hataları önlemeye ve veri bütünlüğünü sağlamaya yardımcı olur.

Bu kısıtı ekleme, diğerlerinde olduğu gibi yapılır.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);

Ancak bizim durumumuzda, NOT NULL kısıtını da dayatmak istiyoruz. Tek bir sütunda birden çok kısıtı araya boşluk koyarak birlikte uygulayabiliriz: 

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);

Sıralamanın önemli olmadığını unutmayın; NOT NULL UNIQUE de kullanabilirdik.

Var olan bir tabloya UNIQUE kısıtı ekleme şu şekilde yapılır:

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);

Birden fazla sütunda UNIQUE kısıtı

courses tablosunda her bölümün ders adının benzersiz olmasını sağlamak istediğimizi varsayalım. Bu durumda, course_name ve department sütunlarının birlikte benzersiz olması gerekir.

Birden fazla sütun söz konusu olduğunda, kısıt CREATE TABLE komutunun sonunda eklenir:

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);

Alternatif olarak, kısıtı mevcut bir tabloyu değiştirerek de ekleyebiliriz. Bu durumda, sütun adlarını bir demet (tuple) olarak veririz:

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);

NOT NULL UNIQUE ve PRIMARY KEY kısıtları 

PRIMARY KEY kısıtının hem benzersizliği hem de eksik değer olmamasını sağladığını öğrendik. O halde şu ikisi arasındaki farkın ne olduğunu merak ediyor olabilirsiniz:

course_id INT PRIMARY KEY

course_id INT UNIQUE NOT NULL

Bir tablodaki NOT NULL UNIQUE ile PRIMARY KEY arasındaki fark, amaçları ve kullanım şekilleridir. 

Her ikisi de bir sütunda (veya sütunlarda) benzersizlik ve boş olmama zorunluluğunu dayatsa da, bir tabloda yalnızca bir PRIMARY KEY bulunabilir ve bu, tablodaki her kaydı benzersiz olarak tanımlamak için tasarlanmıştır.

Öte yandan, NOT NULL UNIQUE kombinasyonu, iş kurallarına özgü veri bütünlüğünü sağlamak amacıyla null değerler olmadan her satırda benzersiz bir değer zorlamak için ek sütunlara uygulanabilir. Bir tabloda herhangi sayıda NOT NULL UNIQUE kısıtı bulunabilir.

Her ikisinin birden bulunması, veritabanı tasarımında daha fazla esneklik sağlar; bir kaydın birincil tanımlayıcısı ile tablodaki diğer önemli ve benzersiz nitelikler arasında ayrım yaparken benzersizliği ve veri bütünlüğünü dayatmak için birden çok yol sunar.

DEFAULT Kısıtı

Öğrenciler, üniversite kaydından sonra ana dallarını seçmek için biraz zamana ihtiyaç duyabilir. Üniversite, ana dal sütununun değeri, henüz ana dalını seçmemiş öğrenciler için 'Undeclared' (belirtilmemiş) dizesi olsun istiyor.

Bunu yapmak için, DEFAULT kısıtını kullanarak bu sütun için bir varsayılan değer ayarlayabiliriz. students tablosunu şu şekilde değiştirebiliriz:

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';

Bunun yerine, tablo oluşturulurken bir DEFAULT kısıtı ayarlamak istersek, bunu sütun veri türünden sonra belirterek yapabiliriz:

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);

CHECK Kısıtı

Bu üniversitede notlar 0 ile 100 arasındadır. Herhangi bir kısıt olmadan, enrollment tablosundaki grade sütunu herhangi bir tamsayı değeri kabul eder. Bunu, değerlerin 0 ile 100 arasında olmasını dayatmak için bir CHECK kısıtı kullanarak düzeltebiliriz.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);

Genel olarak, CHECK kısıtları, verilerin sağlamasını istediğimiz belirli koşulları doğrulamamızı sağlar. Bunlar, veri tutarlılığı ve bütünlüğünü sağlamak için önemlidir.

Bir CHECK kısıtı birden fazla sütunu kapsayabilir. Bunu, grade ve is_passing_grade değerlerinin tutarlı olmasını sağlamak için kullanalım. Diyelim ki bir notun geçer sayılması için en az 60 olması gerekir. O halde, is_passing_grade değerinin ancak ve ancak not en az 60 ise TRUE olmasını garanti edebiliriz. CHECK kısıtlarının CREATE TABLE komutunda nasıl tanımlandığını göstermek için bunu tablo oluşturma sırasında yapalım:

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);

Yukarıdaki kısıtta bir sorun var. Bir öğrenci bir derse kayıt olduğunda henüz bir notu olmayacaktır. Bu nedenle, not için NULL değerlere izin vermeliyiz. Bunu yaparken, not hâlâ tanımsız olduğunda geçme notu kısıtını da NULL olacak şekilde güncellememiz gerekir. Bunu hesaba katacak şekilde kısıtı şu şekilde güncelleyebiliriz:

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);

grade ve is_passing_grade sütunlarının her ikisinde de veri türüne ve varsayılan değere NULL eklediğimize dikkat edin. Bunların amacı sadece okunabilirliği artırmaktır.

Şimdi bir CHECK kısıtıyla hangi koşulları dayatabileceğimize bakalım.

Aralık koşulları

Bir sütundaki değerlerin belirli bir aralıkta olmasını sağlayabiliriz.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);

Liste koşulları

Bir sütunun değerinin belirli bir değer listesinden biriyle eşleştiğini doğrulayabiliriz.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));

Karşılaştırma koşulları

Bir sütundaki değerleri, belirtilen bir koşulu (büyüktür, küçüktür vb.) sağladığından emin olmak için karşılaştırabiliriz.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);

Desen eşleme koşulları

LIKE veya SIMILAR TO gibi desen eşleme yöntemlerini metin verilerini doğrulamak için kullanabiliriz.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');

Mantıksal koşullar

Mantıksal işleçleri (AND, OR, NOT) kullanarak birden çok koşula izin verebiliriz.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);

Bileşik koşullar

Birden fazla sütun üzerinde bir koşul uygular.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);

FOREIGN KEY Kısıtı

Yabancı anahtar kısıtları, iki tablonun sütunlarını birbirine bağlamak ve verilerin başvuru bütünlüğünü sağlamak için kullanılır. Özetle, bir tablodaki yabancı anahtar, diğer bir tablodaki birincil anahtarı işaret eder ve bu iki tablodaki satırların ilişkili olduğunu belirtir. Bu, yabancı anahtarı olan bir tabloda, bağlı tablodaki birincil anahtara karşılık gelmeyen bir satıra sahip olamayacağınız anlamına gelir.

Örneğimizde, enrollments tablosundaki her kayıt, sırasıyla student_id ve course_id sütunları aracılığıyla bir öğrenciye ve bir derse atıfta bulunur. Herhangi bir kısıt olmadığında, enrollments tablosundaki bu tanımlayıcıların değerlerinin students ve courses tablolarındaki mevcut girişlerle eşleşmesini garanti eden bir şey yoktur.

enrollments tablosunu oluştururken bunu şu şekilde güvence altına alırız:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Bunların yabancı anahtar olabilmesi için, sırasıyla students ve courses tablolarında birincil anahtar olmaları gerektiğini unutmayın.

Diğer kısıtlarda olduğu gibi, bunları tablo oluşturulduktan sonra ALTER TABLE komutunu kullanarak da tanımlayabiliriz:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

Hepsini Bir Araya Getirme

Bu yazı boyunca çeşitli bütünlük kısıtlarını ve bunları bir üniversite veritabanını iyileştirmek için nasıl kullanacağımızı gösterdik. Öğrendiklerimizi birleştiren CREATE TABLE komutunun son sürümü aşağıdadır.

Önce, students tablosunu tanımlayalım:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);

Şimdi, courses tablosunu tanımlayalım:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);

Son olarak, öğrenciler ve dersler arasındaki ilişkileri kuran enrollments tablosunu tanımlayalım:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);

Bu son örneğe birkaç kısıt ekledik. Bir üniversitenin bilinen bir bölüm ve ana dal kümesi vardır. Dolayısıyla, major ve department sütunlarının alabileceği değerler sonlu ve önceden bilinir. Bu gibi durumlarda, sütunların yalnızca bu bilinen değer kümesinden değerler almasını sağlamak için bir CHECK kısıtı kullanılması önerilir.

Sonuç

Bu yazıda, SQL'deki farklı bütünlük kısıdı türlerini ve bunları PostgreSQL kullanarak nasıl uygulayacağımızı inceledik. Birincil anahtarları, NOT NULL kısıtlarını, UNIQUE kısıtlarını, DEFAULT kısıtlarını, CHECK kısıtlarını ve FOREIGN KEY kısıtlarını ele alarak her biri için pratik örnekler sunduk.

Bu kavramları anlayarak verilerimizin doğruluğunu, tutarlılığını ve güvenilirliğini sağlayabiliriz.

Verileri verimli şekilde düzenleme hakkında daha fazla bilgi edinmek istiyorsanız, şu Veritabanı Tasarımı kursuna göz atın.