Foreign Keys - PRAGMA foreign

Foreign Key nima?

Foreign Key - bu ikki jadval orasidagi bog'lanishni belgilaydigan ustun. U ma'lumotlar yaxlitligini ta'minlaydi va bog'langan jadvallar orasidagi munosabatlarni boshqaradi.

1. Foreign Key yoqish

🔗 Foreign Key yoqish

Foreign Key yoqish:
            -- Foreign Key constraint-larni yoqish
PRAGMA foreign_keys = ON;

-- Foreign Key holatini tekshirish
PRAGMA foreign_keys;
          
Natija:
            
                  Holat
                  Qiymat
                  Tushuntirish
                
                  Yoqilgan
                  1
                  Foreign Key constraint-lar faol
                
                  O'chirilgan
                  0
                  Foreign Key constraint-lar o'chirilgan

Holat	Qiymat	Tushuntirish
Yoqilgan	1	Foreign Key constraint-lar faol
O'chirilgan	0	Foreign Key constraint-lar o'chirilgan

💡 Muhim eslatma

Foreign Key constraint-lar default holatda o'chirilgan bo'ladi. Har doim yoqish kerak!

2. Foreign Key yaratish

# Jadvallar yaratish
CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    birth_year INTEGER,
    nationality TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER,
    isbn TEXT UNIQUE,
    publication_year INTEGER,
    pages INTEGER,
    price REAL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

# Foreign Key yoqish
PRAGMA foreign_keys = ON;

3. Amaliy misollar

# Kutubxona tizimi
sqlite3 library.db

# Foreign Key yoqish
PRAGMA foreign_keys = ON;

# Jadvallar yaratish
CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    birth_year INTEGER,
    nationality TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER,
    isbn TEXT UNIQUE,
    publication_year INTEGER,
    pages INTEGER,
    price REAL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE book_categories (
    book_id INTEGER,
    category_id INTEGER,
    PRIMARY KEY (book_id, category_id),
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE book_loans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    book_id INTEGER,
    borrower_name TEXT,
    loan_date DATE DEFAULT CURRENT_DATE,
    return_date DATE,
    is_returned BOOLEAN DEFAULT 0,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

# Ma'lumotlar qo'shish
INSERT INTO authors (name, birth_year, nationality) VALUES 
('Alisher Navoiy', 1441, 'O''zbek'),
('Abdulla Avloniy', 1878, 'O''zbek'),
('Chingiz Aytmatov', 1928, 'Qirg''iz'),
('Leo Tolstoy', 1828, 'Rus');

INSERT INTO books (title, author_id, isbn, publication_year, pages, price) VALUES 
('Xamsa', 1, '978-1234567890', 1495, 500, 25.50),
('Turkiy guliston', 1, '978-1234567891', 1499, 300, 20.00),
('O''zbek tili grammatikasi', 2, '978-1234567892', 1920, 200, 15.75),
('Jamilia', 3, '978-1234567893', 1958, 150, 12.00),
('War and Peace', 4, '978-1234567894', 1869, 1200, 35.00);

INSERT INTO categories (name, description) VALUES 
('Poetry', 'She''r va nazm'),
('Grammar', 'Til grammatikasi'),
('Fiction', 'Badiiy asar'),
('History', 'Tarixiy asar');

INSERT INTO book_categories (book_id, category_id) VALUES 
(1, 1),  -- Xamsa - Poetry
(2, 1),  -- Turkiy guliston - Poetry
(3, 2),  -- O'zbek tili grammatikasi - Grammar
(4, 3),  -- Jamilia - Fiction
(5, 3);  -- War and Peace - Fiction

INSERT INTO book_loans (book_id, borrower_name, loan_date, return_date, is_returned) VALUES 
(1, 'Ali Valiyev', '2023-01-15', '2023-02-15', 1),
(2, 'Malika Karimova', '2023-02-01', NULL, 0),
(3, 'Bobur Toshmatov', '2023-01-20', '2023-02-20', 1),
(1, 'Zarina Qodirova', '2023-03-01', NULL, 0),
(4, 'Ahmad Karimov', '2023-02-15', '2023-03-15', 1);

4. Foreign Key tekshirish

# Foreign Key constraint-larni tekshirish
PRAGMA foreign_key_check;

# Belgili jadval uchun
PRAGMA foreign_key_check(books);

# Foreign Key ma'lumotlari
PRAGMA foreign_key_list(books);

# Natija:
# 0|author_id|authors|id|NO ACTION|NO ACTION|NONE

# Barcha jadvallar uchun
SELECT name FROM sqlite_master WHERE type = 'table';
PRAGMA foreign_key_list(authors);
PRAGMA foreign_key_list(books);
PRAGMA foreign_key_list(categories);
PRAGMA foreign_key_list(book_categories);
PRAGMA foreign_key_list(book_loans);

5. Foreign Key xatolari

# Xato: Mavjud bo'lmagan author_id
INSERT INTO books (title, author_id) VALUES ('Test Book', 999);
-- Xato: FOREIGN KEY constraint failed

# Xato: Mavjud bo'lmagan book_id
INSERT INTO book_loans (book_id, borrower_name) VALUES (999, 'Test User');
-- Xato: FOREIGN KEY constraint failed

# Xato: Mavjud bo'lmagan category_id
INSERT INTO book_categories (book_id, category_id) VALUES (1, 999);
-- Xato: FOREIGN KEY constraint failed

# To'g'ri: Mavjud author_id
INSERT INTO books (title, author_id) VALUES ('Test Book', 1);
-- Ishlamaydi

6. Murakkab misollar

# E-commerce tizimi
sqlite3 ecommerce.db

# Foreign Key yoqish
PRAGMA foreign_keys = ON;

# Jadvallar yaratish
CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    registration_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    category_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount REAL,
    status TEXT DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    price REAL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

# Ma'lumotlar qo'shish
INSERT INTO customers (name, email, phone) VALUES 
('Ali Valiyev', '[email protected]', '+998901234567'),
('Malika Karimova', '[email protected]', '+998901234568'),
('Bobur Toshmatov', '[email protected]', '+998901234569');

INSERT INTO categories (name, description) VALUES 
('Electronics', 'Electronic devices and accessories'),
('Clothing', 'Fashion and clothing items'),
('Books', 'Books and educational materials');

INSERT INTO products (name, price, stock_quantity, category_id) VALUES 
('Laptop', 1000.00, 10, 1),
('Phone', 500.00, 25, 1),
('Book', 20.00, 100, 3),
('T-shirt', 30.00, 50, 2);

INSERT INTO orders (customer_id, total_amount, status) VALUES 
(1, 150.00, 'completed'),
(2, 200.00, 'completed'),
(1, 100.00, 'pending'),
(3, 300.00, 'completed');

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES 
(1, 1, 1, 150.00),
(2, 2, 1, 200.00),
(3, 3, 2, 50.00),
(4, 1, 1, 300.00);

7. Foreign Key operatsiyalari

⚙️ Foreign Key operatsiyalari

🗑️ CASCADE DELETE

CASCADE

Agar parent o'chirilsa, child ham o'chiriladi

FOREIGN KEY (parent_id) 
REFERENCES parent(id) 
ON DELETE CASCADE

🔗 SET NULL

SET NULL

Agar parent o'chirilsa, child da NULL bo'ladi

FOREIGN KEY (parent_id) 
REFERENCES parent(id) 
ON DELETE SET NULL

🚫 RESTRICT

RESTRICT

Agar child mavjud bo'lsa, parent o'chirilmaydi

FOREIGN KEY (parent_id) 
REFERENCES parent(id) 
ON DELETE RESTRICT

⏸️ NO ACTION

NO ACTION

Default holat - o'chirishni cheklaydi

FOREIGN KEY (parent_id) 
REFERENCES parent(id) 
ON DELETE NO ACTION

📊 Jadval munosabatlari diagrammasi

Authors

id (PK)

name

birth_year

→

Books

id (PK)

title

author_id (FK)

→

Book_Loans

id (PK)

book_id (FK)

borrower_name

8. Amaliy loyiha

# Blog tizimi
sqlite3 blog.db

# Foreign Key yoqish
PRAGMA foreign_keys = ON;

# Jadvallar yaratish
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    author_id INTEGER,
    is_published BOOLEAN DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE post_categories (
    post_id INTEGER,
    category_id INTEGER,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER,
    author_id INTEGER,
    content TEXT NOT NULL,
    is_approved BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE post_tags (
    post_id INTEGER,
    tag_id INTEGER,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

# Ma'lumotlar qo'shish
INSERT INTO users (username, email, password_hash) VALUES 
('admin', '[email protected]', 'hashed_password_1'),
('author1', '[email protected]', 'hashed_password_2'),
('author2', '[email protected]', 'hashed_password_3');

INSERT INTO categories (name, description) VALUES 
('Programming', 'Dasturlash haqida'),
('Technology', 'Texnologiya yangiliklari'),
('Education', 'Ta''lim va o''qish');

INSERT INTO posts (title, content, author_id, is_published, view_count) VALUES 
('Python dasturlash', 'Python dasturlash tili haqida...', 1, 1, 150),
('JavaScript asoslari', 'JavaScript dasturlash tili...', 2, 1, 200),
('O''zbekiston tarixi', 'O''zbekiston tarixi haqida...', 3, 1, 100);

INSERT INTO post_categories (post_id, category_id) VALUES 
(1, 1),  -- Python dasturlash - Programming
(2, 1),  -- JavaScript asoslari - Programming
(3, 3);  -- O'zbekiston tarixi - Education

INSERT INTO comments (post_id, author_id, content, is_approved) VALUES 
(1, 2, 'Juda foydali maqola!', 1),
(1, 3, 'Rahmat, ko''p narsa o''rgandim', 1),
(2, 1, 'JavaScript haqida ko''proq yozing', 0);

INSERT INTO tags (name) VALUES 
('Python'), ('JavaScript'), ('Programming'), ('Technology'), ('Education');

INSERT INTO post_tags (post_id, tag_id) VALUES 
(1, 1),  -- Python dasturlash - Python
(1, 3),  -- Python dasturlash - Programming
(2, 2),  -- JavaScript asoslari - JavaScript
(2, 3);  -- JavaScript asoslari - Programming

9. Foreign Key monitoring

# Foreign Key monitoring script
import sqlite3

def check_foreign_keys(db_path):
    """Foreign Key constraint-larni tekshirish"""
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        
        # Foreign Key holatini tekshirish
        cursor.execute("PRAGMA foreign_keys")
        fk_status = cursor.fetchone()[0]
        print(f"Foreign Keys: {'ON' if fk_status else 'OFF'}")
        
        # Barcha jadvallar
        cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
        tables = cursor.fetchall()
        
        for table in tables:
            table_name = table[0]
            print(f"\n{table_name} jadvali:")
            
            # Foreign Key ma'lumotlari
            cursor.execute(f"PRAGMA foreign_key_list({table_name})")
            fk_list = cursor.fetchall()
            
            if fk_list:
                for fk in fk_list:
                    print(f"  - {fk[3]} -> {fk[2]}.{fk[4]}")
            else:
                print("  - Foreign Key yo'q")
        
        # Foreign Key constraint-larni tekshirish
        cursor.execute("PRAGMA foreign_key_check")
        violations = cursor.fetchall()
        
        if violations:
            print(f"\nForeign Key xatolari: {len(violations)}")
            for violation in violations:
                print(f"  - {violation}")
        else:
            print("\nForeign Key constraint-lar to'g'ri")

# Foydalanish
check_foreign_keys('blog.db')

10. Eng yaxshi amaliyotlar

✅ Foreign Key yoqish: PRAGMA foreign_keys = ON
✅ Ma'lumotlar yaxlitligi: Constraint-larni tekshirish
✅ CASCADE DELETE: Bog'langan ma'lumotlarni o'chirish
✅ SET NULL: Bog'lanishni uzish
✅ RESTRICT: O'chirishni cheklash
✅ Monitoring: Foreign Key holatini tekshirish

Keyingi bo'lim: Yakuniy Bosqich →

🔗 Foreign Keys - PRAGMA foreign_keys = ON

Foreign Key nima?

1. Foreign Key yoqish

🔗 Foreign Key yoqish

Foreign Key yoqish:

Natija:

💡 Muhim eslatma

2. Foreign Key yaratish

3. Amaliy misollar

4. Foreign Key tekshirish

5. Foreign Key xatolari

6. Murakkab misollar

7. Foreign Key operatsiyalari

⚙️ Foreign Key operatsiyalari

🗑️ CASCADE DELETE

🔗 SET NULL

🚫 RESTRICT

⏸️ NO ACTION

📊 Jadval munosabatlari diagrammasi

Authors

Books

Book_Loans

8. Amaliy loyiha

9. Foreign Key monitoring

10. Eng yaxshi amaliyotlar