(Postgre)SQL concepts and terms
A glossary of terms related to PostgreSQL® and SQL in general.
Stay updated with Aiven
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
Loading...
Loading...
A glossary of terms related to PostgreSQL® and SQL in general.
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
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.
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.
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.
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.
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 makes large tables more manageable. This way, you can access or manage only subsets of data, but still maintain the integrity of the table.
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.
An established association between tables; see also Foreign key.
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 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.
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.
A transaction is unit of work applied to a database, containing a sequence of operations.
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.
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!
