15. Views & Indexes Reference

Database Objects and Structures.

VIEW

Return: Value

Stored virtual queries representation.

Syntax signature:CREATE VIEW view_name AS SELECT ...

Code snippet:

python

CREATE VIEW active_users_view AS SELECT id, name FROM users WHERE status = 'Active';

Expected Output:Logical view registered.

Remember: Always ensure correct syntax formatting when calling VIEW.

INDEX

Return: Value

Speed up column scanning and queries.

Syntax signature:CREATE INDEX idx_name ON table (column);

Code snippet:

python

CREATE INDEX idx_users_email ON users (email);

Expected Output:Index tree generated.

Remember: Always ensure correct syntax formatting when calling INDEX.

PRIMARY KEY

Return: Value

Enforce row uniqueness.

Syntax signature:CREATE TABLE t (id INT PRIMARY KEY);

Code snippet:

python

CREATE TABLE employees (id INT PRIMARY KEY, name TEXT);

Expected Output:Primary key constraint registered.

Remember: Always ensure correct syntax formatting when calling PRIMARY KEY.

FOREIGN KEY

Return: Value

Enforce reference validation.

Syntax signature:CREATE TABLE t (fk INT REFERENCES t2(id));

Code snippet:

python

CREATE TABLE orders (id INT, user_id INT REFERENCES users(id));

Expected Output:Foreign key constraint registered.

Remember: Always ensure correct syntax formatting when calling FOREIGN KEY.

UNIQUE

Return: Value

Enforce column uniqueness constraint.

Syntax signature:CREATE TABLE t (col TEXT UNIQUE);

Code snippet:

python

CREATE TABLE users (email TEXT UNIQUE);

Expected Output:Uniqueness constraint registered.

Remember: Always ensure correct syntax formatting when calling UNIQUE.