Bits of .NET

🔑 Primary Keys = Data Uniqueness

Data needs uniqueness. Primary keys identify records. Unique, indexed, essential.

📝 Primary Key Basics

# Single column primary key
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

# Auto-increment primary key
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

# Composite primary key
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

# Surrogate key vs Natural key
# Surrogate: Auto-generated id
# Natural: Natural identifying column

# Primary key properties
- Unique
- Not null
- Immutable
- Indexed

# Identity columns
CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_date DATE
);

# Sequence (PostgreSQL)
CREATE SEQUENCE user_id_seq;
CREATE TABLE users (
    id INT DEFAULT nextval('user_id_seq') PRIMARY KEY,
    name VARCHAR(100)
);

🎯 Primary Key Best Practices

# Use surrogate keys
id INT PRIMARY KEY AUTO_INCREMENT

# Avoid natural keys
-- Bad: Social Security Number
-- Good: Auto-generated id

# Use BIGINT for large tables
id BIGINT PRIMARY KEY AUTO_INCREMENT

# Choose integer over GUID
-- Integer: Smaller, faster
-- GUID: Unique, distributed

# Add primary key after creation
ALTER TABLE users ADD PRIMARY KEY (id);

# Check primary key
SHOW KEYS FROM users WHERE Key_name = 'PRIMARY';

# Primary key vs Unique key
- Primary Key: One per table, not null
- Unique Key: Multiple, can be null

# Best Practices
- Use auto-increment integer
- Never update primary keys
- Use for foreign key references
- Index automatically created
- Use for joins

💡 Primary Key Tips

• Use auto-increment integer
• Never update primary keys
• Use for foreign key references
• Index automatically created
• Choose surrogate over natural