Database Keys, Constraints, Views, and Transactions: SQL Fundamentals

Primary keys (PK)

PK should be: unique, NOT NULL, stable (doesn't change), narrow (small storage).

Surrogate key

Surrogate key = system-assigned ID with no business meaning (e.g., UserID INT IDENTITY, GUID).

Composite PK example

(StudentID, CourseID) for an Enrollments table (each student can enroll in a course once).

Foreign keys (FK)

FKs connect child → parent.

Good patterns for FKs

Chapters.BookID → Books.BookID, Loans.MemberID → Members.MemberID, Sessions.InstructorID → Instructors.InstructorID.

1NF

1NF: each column is atomic (e.g., store FirstName and LastName, not a single FullName blob).

2NF

2NF: no partial dependency on part of a composite key.

3NF

3NF: no transitive dependency (non-key → non-key).

UNIQUE constraint example

-- UNIQUE username in an app ALTER TABLE Accounts ADD CONSTRAINT UQ_Accounts_Username UNIQUE (Username);

CHECK constraint example

-- Quantity must be non-negative ALTER TABLE Inventory ADD CONSTRAINT CK_Inventory_Quantity CHECK (Quantity >= 0);

Data type tip

email-like strings up to 254 → VARCHAR(254) (saves space vs CHAR, and TEXT is legacy).

Views

A view stores a query definition, not a permanent copy of rows.

Updatability of views

Simple single-table views that keep the PK are often updatable; views with DISTINCT, aggregates, GROUP BY, etc., are typically not.

Simple projection view example

CREATE VIEW dbo.vBooks AS SELECT BookID, Title FROM dbo.Books;

Drop a view

DROP VIEW dbo.vReadingList;

Stored Procedures

Encapsulation of business logic, central permissions (e.g., GRANT EXECUTE), reusable across apps; can return multiple result sets.

Stored Procedure naming

Use schema + verb-noun style: dbo.uspIssueLoan. Avoid sp_ prefix.

Valid parameters example

@MemberID INT, @MinPages INT, @Email NVARCHAR(100).

Calling a stored procedure

EXEC dbo.uspFindMember 42, 'ACTIVE'; -- positional EXEC dbo.uspFindMember @Status = 'ACTIVE', @MemberID = 42; -- named.

Create Procedure example

CREATE PROCEDURE dbo.uspBooksByMinPages @MinPages INT AS BEGIN SELECT BookID, Title, PageCount FROM dbo.Books WHERE PageCount >= @MinPages; END;

Transactions

BEGIN TRANSACTION; ... INSERT/UPDATE/DELETE work here ... COMMIT; -- make permanent or ROLLBACK; -- undo since BEGIN TRANSACTION.

ACID properties

Atomicity: all or nothing. Consistency: constraints remain true before/after. Isolation: concurrent work doesn't see your in-flight changes. Durability: committed data survives crashes.

ERD notation

Fork (crow's foot): "many", Single bar: "exactly one", Open circle: "optional".

Transaction sandbox example

BEGIN TRANSACTION; UPDATE Inventory SET Quantity = Quantity - 1 WHERE ItemID = 9001; -- Validate results here... ROLLBACK; -- or COMMIT when sure.