Embedded Files
DATABASE /
1. DATA TYPES
Numeric:
INT, INTEGER: whole numbers
BIGINT: large whole number, useful for large datasets
DECIMAL(length, decimal_places), NUMERIC(length, decimal_places): fixed-point numbers, ideal for financial data requiring precision
FLOAT, REAL: approximate, floating-point numbers, for scientific calculations
Character:
CHAR(n): fixed-length strings, if the stored text is shorter, it pads the remaining space
VARCHAR(length): Variable-length strings, no extra space is used if the text is shorter
TEXT: long texts, maximum length depends on the database system
Date and time:
DATE
TIME
DATETIME, TIMESTAMP
INTERVAL: time intervals or durations, supported by databases like PostgreSQL
Boolean:
BOOLEAN
Binary (binary data such as images or files):
BLOB (Binary Large Object): suitable for multimedia or encrypted data
VARBINARY(n): variable-length binary data
JSON and XML:
JSON
XML
Specialized:
UUID: Universally unique identifier, used for storing unique keys
GEOGRAPHY, GEOMETRY: spatial data like coordinates, points, or polygons (latitude/longitude), supported only by certain databases like PostgreSQL or SQL Server
EXAMPLE FOR CREATING COLUMNS:
2. TABLES
Column types are dependent on the database. Relational databases have common field types.
The ID marks the identification column. Its values will be unique and countable.
The PRIMARY KEY marks which column will be used as primary key.
The DEFAULT clause defines the default value on a column. This value will be added when a new row is created but the specific column does not receive data. Without it the column receives NULL.
EXAMPLE FOR CREATE SYNTAX:
EXAMPLE FOR CREATE:
3. STORED PROCEDURES
A query can be saved and called in any time later. These are reusable code blocks.
The IN clause defines input parameter.
EXAMPLE FOR CREATING AND CALLING STORED PROCEDURES:
4. VIEWS
Views are virtual tables based on SQL queries. They stay live after executing the query, making it easy to apply more queries on it. Referencing it happens as it would be a table.
Views stay in the database until they are dropped. They will not contain actual data, once they are created, they must be used as a normal table.
Altering a view (not all databases support it):
ALTER VIEW (SQL Server)
CREATE OR REPLACE VIEW
EXAMPLE FOR CREATING, ALTERING, MODIFYING ITS DATA, DROPPING A VIEW:
5. KEYS AND CONSTRAINTS
A constraint create limits how data can be handled on a column.
5.1. PRIMARY KEY
A primary key uniquely identifies each record in a table. It ensures that no duplicate rows exist and that no column in the primary key contains a NULL value. Furthermore:
a table can have only one primary key
a primary key can consist of a single column or a combination of columns (composite key)
database systems create an index on the column of the primary key automatically (implicit index definition)
the simplest way is using an inline definition, the system will use default index type and automatically generated index name
in case you need named index or not the default index type as a primary key, use CONSTRAINT instead (explicit index definition)
in case you need to manage the keys (usually you need), create named keys
EXAMPLE FOR CREATING A TABLE WITH PRIMARY KEY:
EXAMPLE FOR CREATING A TABLE WITH COMPOSITE KEY:
EXAMPLE FOR CREATING A TABLE WITH NAMED PRIMARY KEY:
5.2. FOREIGN KEY
A foreign key is a column or a set of columns that establishes a relationship between two tables by referencing the primary key in another table. This means that a record which has a foreign key, refers to an other record in an other table by its primary key.
A foreign key must reference an existing primary key value in the parent table or having NULL.
A table can have multiple foreign keys.
Foreign keys can have NULL values.
To retrieve data from multiple tables which are connected by a foreign key reference, use a JOIN.
Performance considerations:
some databases will not create index on foreign keys (PostgreSQL, SQL Server), create them manually
ON DELETE CASCADE and ON UPDATE CASCADE can be expensive if the parent table has many related records (consider using ON DELETE SET NULL or manual deletions on large data)
foreign key validation happens on each row, this slows down bulk (higher amount of) inserts and updates (disable foreign key checks for bulk operations)
on high-concurrency transactions the parent row and all the child rows are locked (consider using optimistic locking or batching updates)
foreign keys do not always work well with partitioned tables (especially in PostgreSQL and MySQL) (use alternative methods, such as manually enforcing constraints with triggers)
some databases (PostgreSQL, Oracle) support deferred constraints, allowing constraint checking at transaction commit rather than immediately (asynchronous constraint checking) (use DEFERRABLE INITIALLY DEFERRED in PostgreSQL)
EXAMPLE FOR CREATING A TABLE WITH FOREIGN KEY:
Automatism:
referential integrity
automatic index creation
cascading actions
constraint validation
deferred constraint checking
Referential integrity:
ensures that a foreign key must reference an existing primary key value in the parent table
prevents inserting orphan records (e.g. values in the foreign key column that do not exist in the referenced table)
EXAMPLE FOR REFERENTIAL INTEGRITY:
Automatic index creation:
in PostgreSQL and SQL Server: a foreign key column does NOT automatically create an index, but indexing is recommended for performance
in MySQL (InnoDB): a foreign key implicitly creates an index if one does not already exist
Cascading actions:
ON DELETE CASCADE: automatically deletes dependent records if the referenced row is deleted
ON DELETE SET NULL: sets the foreign key column to NULL when the referenced row is deleted
ON UPDATE CASCADE: automatically updates dependent records if the referenced primary key value changes
EXAMPLE FOR CASCADING ACTIONS:
Constraint validation:
prevents inserting or updating a foreign key value that does not match an existing primary key
prevents deletion of referenced rows unless ON DELETE CASCADE or SET NULL is used
Deferred constraint checking (PostgreSQL, Oracle):
allows foreign key constraints to be checked at commit time instead of immediately
useful for bulk operations where temporary violations occur but are resolved later
EXAMPLE FOR USING FOREIGN KEY AUTOMATISM:
EXAMPLE FOR HANDLING BULK OPERATIONS:
EXAMPLE FOR DEFERRED CONSTRAINT CHECKING (POSTGRESQL):
5.3. UNIQUE, CHECK, NOT NULL CONSTRAINTS
The UNIQUE constraint ensures that all values in a column (or a set of columns) are distinct, preventing duplicate entries. Unlike the primary key, a table can have multiple unique constraints. Furthermore:
columns with unique constraints can have NULL values, but only one NULL per column (depending on the database)
useful for enforcing rules like unique emails or usernames
it creates an un-named index in the background optimized for uniqueness but not optimized for querying
The CHECK constrain checks the value during adding.
The NOT NULL constraint prevents adding null value.
EXAMPLE FOR CREATING A TABLE WITH CONSTRAINTS:
6. INDEXES
Index is usually a co-table connected to a table, helping in fast search if the checked column. It contains pointers to the rows in a table.
Ideal for optimizing large datasets.
Avoid over-indexing, it can slow down operations.
Implicit index: created automatically when triggered by PRIMARY KEY, UNIQUE, FOREIGN KEY constraints.
Explicit index: created with explicit instructions, in dedicated line.
Database systems automatically create index on primary keys:
MySQL: clustered
SQL Server: clustered by default but non-clustered is specifiable
PostgreSQL: unique B-tree
Oracle: unique B-tree
SQLite: implicit B-tree
Database systems automatically create index on UNIQUE constraint as well (non-primary key, implicit index). This index is for ensuring uniqueness, not for fats searches.
Some databases (e.g. PostgreSQL) create index on FOREIGN KEY columns as well (non-primary key, implicit index).
Default index type on non-primary key columns:
MySQL: B-tree (unless defined as FULLTEXT or SPATIAL)
SQL Server: non-clustered
PostgreSQL: B-tree (unless defined as Hash, GiST etc.)
Oracle: B-tree (unless defined as Bitmap)
SQLite: B-tree
Clustered index:
defines the physical order of the data in the table, therefore
there can be only one clustered index per table
searching for data is very fast, especially for range queries
Non-clustered index:
does NOT change the physical order of the data in the table
it creates a separate structure that holds pointers to the actual rows in the table
there can be multiple non-clustered indexes on a table
Unique index:
prevents duplications (ensures uniqueness)
B-tree index:
ensures efficient retrieval (single row and range) and sorting
Unique B-tree index:
ensures uniqueness, and efficient retrieval and sorting
You can have one clustered and multiple other kind of indexes on a table.
You can put more indexes on a column, even on IDs if necessary.
You can create an index on multiple columns (composite index). This index will be automatically used when in the WHERE part of the SELECT all columns marked in the definition are present.
Creating unique composite index can be with or without constraint. In case of the constraint version, it is not possible to name the index itself, just the constraint, and there is no further index customization (e.g. not-default index type).
Keywords of CREATE INDEX in database systems are below.
In PostgreSQL, using non-default index types has different aims.
EXAMPLE FOR CREATE INDEX SYNTAXES:
EXAMPLE FOR CREATING DEFAULT INDEX ON NON-PRIMARY KEY COLUMN:
EXAMPLE FOR CREATING DEFAULT COMPOSITE INDEX ON NON-PRIMARY KEY COLUMNS:
EXAMPLE FOR CREATING UNIQUE DEFAULT INDEX ON NON-PRIMARY KEY COLUMN:
EXAMPLE FOR CREATING UNIQUE DEFAULT COMPOSITE INDEX ON NON-PRIMARY KEY COLUMNS:
EXAMPLE FOR CREATING UNIQUE DEFAULT COMPOSITE INDEX ON NON-PRIMARY KEY COLUMNS VIA CONSTRAINT:
EXAMPLE FOR CREATING NON-CLUSTERED INDEX ON PRIMARY KEY COLUMN (IMPLICIT):
EXAMPLE FOR CREATING NON-CLUSTERED INDEX ON NON-PRIMARY KEY COLUMN (EXPLICIT):
EXAMPLE FOR CREATING AN OTHER INDEX TYPE ON EXISTING PRIMARY KEY INDEX (EXPLICIT OVER IMPLICIT):
EXAMPLE FOR CREATING HASH/GIST/GIN/BRIN INDEX TYPES ON NON-PRIMARY KEY COLUMNS (EXPLICIT):
7. PARTITIONS
When using large datasets, it is important to divide the table to partitions. Queries will be executed selectively on one or a few partitions only.
The SQL for defining partitions, needs verbal keywords.
Partition types:
range: based on a range of values
list: based on a predefined list of values
hash: based on a hash function applied to one or more columns
composite: combines two partitioning methods
EXAMPLE FOR PARTITIONING UPON CREATION:
Google Sites
Report abuse