What is Database Constraints?
Certain rules on on data columns on table is called Constraints These rules are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.
There are two ways to define constraints:
1.Table constraints-A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column.
2.Column constraints-A column constraint is defined as part of a column definition.
In this tutorial, you will learn how to use the Constraint CREATE TABLE statement when you create new tables.
CREATE TABLE order_details ( column1 integer CONSTRAINT order_details_pk PRIMARY KEY, column2 integer NOT NULL, );
- First, you specify the name of the new table after the CREATE TABLE clause. The TEMPORARY keyword is for creating a temporary table, which we will discuss in the temporary table tutorial.
- Next, you list the column name, its data type, and column constraint. You can have multiple columns in a table, each column is separated by a comma (,). The column constraint defines the rules for the column e.g., NOT NULL.
- Then, after the column list, you define a table-level constraint that defines rules for the data in the table.
- After that, you specify an existing table from which the new table inherits. It means the new table contains all columns of the existing table and the columns defined in the CREATE TABLE statement. This is a PostgreSQL’s extension to SQL.
The following are the commonly used column constraints in PostgreSQL:
|PRIMARY KEY||this constraint is the combination of NOT NULL and UNIQUE constraints. You can define one column as PRIMARY KEY by using column-level constraint. In case the primary key contains multiple columns, you must use the table-level constraint.|
|UNIQUE||the value of the column must be unique across the whole table. However, the column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.|
|CHECK||CHECK constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.|
|NOT NULL||the value of the column cannot be NULL.|
|DEFAULT||Assign a literal constant default value for the column name, arrange for the default value of column did to be generated by selecting the next value of a sequence object, and make the default value of modtime be the time at which the row is inserted|
|REFERENCES||Constrains the value of the column that exists in a column in another table. You use REFERENCES to define the foreign key constraint.|
Define a unique table constraint for the table unitab. Unique table constraints can be defined on one or more columns of the table:
CREATE TABLE unitab ( code char(5), title varchar(40), did integer, date_prod date, kind varchar(10), len interval hour to minute, CONSTRAINT production UNIQUE(date_prod) );
Define a check table constraint:
CREATE TABLE checktab ( did integer, name varchar(40) CONSTRAINT con1 CHECK (did > 100 AND name <> '') );
Define a primary key table constraint for the table primtab:
CREATE TABLE primtab ( code char(5), title varchar(40), did integer, date_prod date, kind varchar(10), len interval hour to minute, CONSTRAINT code_title PRIMARY KEY(code,title) );
Define a unique constraint for the name column table constraints:
CREATE TABLE uniqtab ( did integer, name varchar(40), UNIQUE(name) );
–Create table films and table distributors:
CREATE TABLE primcol ( code char(5) CONSTRAINT prikey PRIMARY KEY, title varchar(40) NOT NULL, did integer NOT NULL, date_prod date, kind varchar(10), len interval hour to minute ); CREATE TABLE notcol ( did integer PRIMARY KEY DEFAULT nextval('k3'), name varchar(40) NOT NULL CHECK (name <> '') ); ERROR: relation "k3" does not exist
Note that nextval(‘films’) is a table name
CREATE TABLE notcol ( did integer PRIMARY KEY DEFAULT nextval('primcol'), name varchar(40) NOT NULL CHECK (name <> '') );
Define a check column constraint:
CREATE TABLE checkcol ( did integer CHECK (did > 100), name varchar(40) );
Assign a literal constant default value for the column name, arrange for the default value of column did to be generated by selecting the next value of a sequence object, and make the default value of modtime be the time at which the row is inserted:
CREATE TABLE defaultcol( name varchar(40) DEFAULT 'Luso Films', did integer DEFAULT nextval('distributors_serial'), modtime timestamp DEFAULT current_timestamp );
Define two NOT NULL column constraints on the table notcol, one of which is explicitly given a name:
CREATE TABLE notcol ( did integer CONSTRAINT no_null NOT NULL, name varchar(40) NOT NULL );
Define a unique constraint for the name column:
CREATE TABLE uniqcol ( did integer, name varchar(40) UNIQUE );
Define a primary key constraint for table distributors. The following two examples are equivalent, the first using the table constraint syntax, the second the column constraint syntax:
CREATE TABLE primarytable ( did integer, name varchar(40), PRIMARY KEY(did) ); CREATE TABLE primarycolumn ( did integer PRIMARY KEY, name varchar(40) );
create a composite type and a typed table:
CREATE TYPE employee_type AS (name text, salary numeric); CREATE TABLE employees OF employee_type ( PRIMARY KEY (name), salary WITH OPTIONS DEFAULT 1000 );
mostly used constraints:
user_id – primary key
username – unique and not null
password – not null
email – unique and not null
created_on – not null
last_login – null
place,salary -with option default ‘india’,salary WITH OPTIONS DEFAULT 1000
how to checking the existence of a constraint?
postgres=# \d primtab Table "public.primtab" Column | Type | Modifiers -----------+-------------------------+----------- code | character(5) | not null title | character varying(40) | not null did | integer | date_prod | date | kind | character varying(10) | len | interval hour to minute | Indexes: "code_title" PRIMARY KEY, btree (code, title)
if you want see all constraint name and table name(relname) you need to join pg_stat_all_tables and pg_constraint views:
postgres=# select a.conname,a.contype,a.conrelid,b.relname,b.relid from pg_constraint a,pg_stat_all_tables b where a.conrelid=b.relid; conname | contype | conrelid | relname | relid -------------------------+---------+----------+----------------+------- prikey | p | 24676 | films | 24676 production | u | 24697 | k | 24697 distributorc_did_check | c | 24702 | distributorc | 24702 code_title | p | 24710 | primtab | 24710 con1 | c | 24706 | distributorsct | 24706 distributors_pkey | p | 24690 | distributors | 24690 distributors_name_check | c | 24690 | distributors | 24690 (7 rows)