Dorokhov.codes

SQL

SQL (Structured Query Language) is a domain-specific language used in programming and managing relational databases. It provides a standardized way to interact with databases, allowing users to perform various operations such as querying data, updating data, creating and modifying database structures, and managing user access.

Here are some key aspects of SQL:

Aspect	Description
Data Querying	SQL allows users to retrieve data from a database using the SELECT statement. Users can specify which columns they want to retrieve, apply filtering criteria, and sort the results.
Data Manipulation	SQL provides statements for manipulating data within a database. This includes inserting new records into tables (INSERT), updating existing records (UPDATE), and deleting records (DELETE).
Database Definition	SQL allows users to define the structure of a database, including creating tables (CREATE TABLE), defining indexes for efficient data retrieval (CREATE INDEX), and establishing relationships between tables (FOREIGN KEY constraints).
Data Control	SQL includes statements for managing user access to the database. This includes granting and revoking privileges such as SELECT, INSERT, UPDATE, and DELETE on specific database objects to users or roles (GRANT, REVOKE).
Data Integrity	SQL supports constraints that enforce rules on the data stored in a database, ensuring its integrity. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.
Transaction Control	SQL provides commands for managing transactions, which are sequences of database operations that are treated as a single unit of work. These commands include COMMIT (to save changes), ROLLBACK (to undo changes), and SAVEPOINT (to set points within a transaction for later rollback).
Data Aggregation	SQL supports functions for aggregating and summarizing data, such as SUM, AVG, COUNT, MIN, and MAX. These functions are often used in conjunction with the GROUP BY clause to perform analyses and generate reports.

SQL is a powerful tool for working with relational databases, and its syntax is standardized across most database management systems (DBMS), though there may be slight variations and extensions between different implementations. Popular relational database management systems that support SQL include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.

Uniqueness by two fields

Example (pair of id and generator_id will be always unique):

CREATE TABLE IF NOT EXISTS $table_name (
    id INT NOT NULL AUTO_INCREMENT,
    generator_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    views INT NOT NULL DEFAULT '0',
    saves INT NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    UNIQUE KEY unique_generator_name (generator_id, name)  -- Ensure uniqueness
);