(Postgre)SQL concepts and terms

Constraint

Constraints specify the rules that data in a database table has to follow, to ensure that data is accurate and reliable. The common constraints are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT and CREATE INDEX.

Data Type

The data type of a column defines the kind of values it can contain, for example integer, character, date, binary etc. Each column must have a name and a data type.

Foreign Key

A foreign key links two database tables. The foreign key constraint relates a column in the Child table to the primary key column in the Parent table, identifying the relationship between the two tables.

Join

A JOIN clause combines columns from one or more tables into a new table. In ANSI-standard SQL, the five types of join are INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER AND CROSS.

Normalization

To reduce data reduncancy and improve integrity, databases can be structured according to a set of 'normal forms'. Normalization means that the columns and tables are organized in such a way that their dependencies are enforced by the database's integrity constraints.

Partitioning

Partitioning makes large tables more manageable. This way, you can access or manage only subsets of data, but still maintain the integrity of the table.

Primary Key

Constraints placed on a column in a database that ensure that the field uniquely identifies each separate record (row). Its function is to enforce data integrity. When the primary key is used in queries, it provides faster access to data.

Relationship

An established association between tables; see also Foreign key.

Schema

A schema describes the organization of data in a database and the relationships between objects and elements, like tables, procedures, views, functions, and indexes. A schema is the owner of the database objects it describes, whereas a user can be the owner of the schema itself. A database can have multiple schemas.

Stored procedures

Stored procedures provide applications access to commonly-used data validation, access control, or other methods. They are maintained in the data dictionary of a database, and may contain several combined SQL procedures each.

Table

All the data in a database is contained in tables. They are database objects that organize data in rows and columns, rather like a spreadsheet. Each individual record is represented by a row, and the fields it contains are arranged into columns(*).

Each column can have properties that define the kind of data stored in that field, such as data type, uniqueness and so on.

(*) With the exception of columnar databases, which flips this organization.

Transaction

A transaction is unit of work applied to a database, containing a sequence of operations.

Trigger

Triggers execute code in response to transactional or other changes to a table or view: when a new item is entered to an inventory table, a similar item could be entered automatically to each of the prices, reviews, and orders tables.

Views

A view is a virtual table based on the results of a SQL statement. This virtual table can be manipulated and queried exactly like a single database table.

--

To read more about PostgreSQL, take a look at What is PostgreSQL? on the Aiven blog. Or just sign up for a PostgreSQL trial on Aiven at https://console.aiven.io/signup!

Further reading

• Remote read replicas - the what, why, and how

• Aiven for PostgreSQL® read-only replicas

• Aiven for PostgreSQL® connection pooling