Bits of .NET

🔗 PRIMARY KEY = Unique ID. FOREIGN KEY = Reference.

Without foreign keys, you can reference nonexistent rows. Foreign keys enforce referential integrity. Prevent orphaned records.

📝 Create Tables with Keys

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

🎯 ON DELETE Actions

-- ON DELETE CASCADE (delete child when parent deleted)
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- ON DELETE SET NULL (set child's FK to NULL)
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL

-- ON DELETE RESTRICT (prevent parent deletion if child exists)
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

-- ON DELETE NO ACTION (default, similar to RESTRICT)
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION

💡 Benefits

• Prevents orphaned records (order with no user)
• Enforces data consistency
• Automatically deletes child records when parent deleted (CASCADE)
• Database maintains relationships
• Essential for data integrity