PostgreSQL----database table constraints----not Null,default,check

database table has not null,default,check,unique,primary key,foreign KEY six kinds of constraints.

A, NOT NULL----non-null constraint

NULL indicates no data and does not represent a specific numeric value, so null in the database is not equal to NULL. Determines whether a cell in a table is NULL using is NULL or is not NULL, instead of =null or!=null, when a field is set to a NOT NULL constraint, insert must assign a value to the field, otherwise the write is rejected. In some programming languages (such as C) the occurrence of NULL in the query results may be directly as a null pointer, if used improperly, will directly cause the program to crash. So a field to set as much as possible the NOT NULL constraint, or the default constraint, of course, the result of outer join may also introduce NULL, so the development process should be as good as possible protection.

1. Setting a NOT NULL constraint field insert must be assigned, the field without the NOT NULL constraint is not assigned, and Null is automatically populated.

 
/* postgres=# create database test with template = template0 encoding=‘UTF8‘ lc_collate=‘C‘ lc_ctype=‘C‘;
CREATE DATABASE
postgres=# 
postgres=# 
postgres=# 
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table tbl_null (a int not null,b varchar(12));
CREATE TABLE
test=# insert into tbl_null (a,b) values(1,‘1‘);
INSERT 0 1
test=# insert into tbl_null (a) values(2);
INSERT 0 1
test=# insert into tbl_null (b) values(‘3‘);
ERROR:  null value in column "a" violates not-null constraint
DETAIL:  Failing row contains (null, 3).
test=# select * from tbl_null;
 a | b 
---+---
 1 | 1
 2 | 
(2 rows) */

2.NOT null constraint increase

An existing field must first be dropped to a null data row before it is set to a NOT NULL constraint.

 
/* test=# alter table tbl_null alter COLUMN b set not null;
ERROR:  column "b" contains null values
test=# delete from tbl_null where b is null;
DELETE 1
test=# alter table tbl_null alter COLUMN b set not null;
ALTER TABLE
test=# \d tbl_null 
          Table "public.tbl_null"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | not null
 b      | character varying(12) | not null

test=# select * from tbl_null ;
 a | b 
---+---
 1 | 1
(1 row) */

3. Remove the NOT NULL constraint

 
 
/* test=# alter table tbl_null alter COLUMN b drop not null;
ALTER TABLE
test=# \d tbl_null 
          Table "public.tbl_null"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | not null
 b      | character varying(12) | */

Second, default----defaults

Insert a field that does not have an assignment is populated by default (provided the field does not have a NOT NULL constraint), and the default default value is set by default, and insert does not assign a value that will be populated by defaults. In particular, a field that sets a NOT NULL constraint, if given a default constraint, does not make an error even if the insert does not assign a value to the field.

1. Set the default constraint, either directly when you create the table, or modify the field after you create the table, and the field new default constraint can be used regardless of the existing data.

 
/* test=# create table tbl_default(a int not null,b varchar(12) not null default ‘try me‘);
CREATE TABLE
test=# \d tbl_default 
                          Table "public.tbl_default"
 Column |         Type          |                  Modifiers                   
--------+-----------------------+----------------------------------------------
 a      | integer               | not null
 b      | character varying(12) | not null default ‘try me‘::character varying

test=# drop table tbl_default ;
DROP TABLE
test=# create table tbl_default(a int not null,b varchar(12) not null);
CREATE TABLE
test=# alter table tbl_default alter COLUMN b set default ‘try me‘;
ALTER TABLE
test=# \d tbl_default 
                          Table "public.tbl_default"
 Column |         Type          |                  Modifiers                   
--------+-----------------------+----------------------------------------------
 a      | integer               | not null
 b      | character varying(12) | not null default ‘try me‘::character varying */

The assignment is filled with an assignment at 2.INSERT, otherwise it is populated with a default value.

 
/* test=# insert into tbl_default (a,b) values(1,‘aloha‘);
INSERT 0 1
test=# insert into tbl_default (a) values(2);
INSERT 0 1
test=# select * from tbl_default ;
 a |   b    
---+--------
 1 | aloha
 2 | try me
(2 rows) */

3. Modify and delete the default value constraint, and modify the default value to set a default value directly.

 
/* test=# alter table tbl_default alter COLUMN b set default ‘my god‘;
ALTER TABLE
test=# \d tbl_default 
                          Table "public.tbl_default"
 Column |         Type          |                  Modifiers                   
--------+-----------------------+----------------------------------------------
 a      | integer               | not null
 b      | character varying(12) | not null default ‘my god‘::character varying

test=# alter table tbl_default alter COLUMN b drop default;
ALTER TABLE
test=# \d tbl_default 
         Table "public.tbl_default"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | not null
 b      | character varying(12) | not null */

Third, check----inspection constraints

Checks whether the field value satisfies the check condition when insert,update, and rejects the write if it is not satisfied.

Settings for 1.CHECK constraints

 
/* test=# create table tbl_check(a int not null check (a>0),b varchar(12) not null check (b in (‘ab‘,‘Ab‘,‘aB‘,‘AB‘)));
CREATE TABLE
test=# drop table tbl_check ;
DROP TABLE
test=# create table tbl_check
test-# (
test(# a int not null,
test(# b varchar(12) not null,
test(# constraint ck_tbl_check_a check (a > 0),
test(# constraint ck_tbl_check_b check (b in (‘ab‘,‘aB‘,‘Ab‘,‘AB‘))
test(# );
CREATE TABLE
test=# create table tbl_check
(
a int not null,
b varchar(12) not null);
CREATE TABLE
test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0);
ALTER TABLE
test=# alter table tbl_check add constraint ck_tbl_check_b check (b in (‘ab‘,‘aB‘,‘Ab‘,‘AB‘));
ALTER TABLE
test=# \d tbl_check 
          Table "public.tbl_check"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | not null
 b      | character varying(12) | not null
Check constraints:
    "ck_tbl_check_a" CHECK (a > 0)
    "ck_tbl_check_b" CHECK (b::text = ANY (ARRAY[‘ab‘::character varying, ‘aB‘::character varying, ‘Ab‘::character varying, ‘AB‘::character varying]::text[])) */

2. The above table Tbl_check, for example, insert when the value of a must be greater than 0 of the integer, B value can only be in ' AB ', ' ab ', ' ab ', ' ab ' within the range.

 
/* test=# insert into tbl_check (a,b) values(1,‘ab‘);
INSERT 0 1
test=# insert into tbl_check (a,b) values(-1,‘ab‘);
ERROR:  new row for relation "tbl_check" violates check constraint "ck_tbl_check_a"
DETAIL:  Failing row contains (-1, ab).
test=# insert into tbl_check (a,b) values(1,‘ac‘);
ERROR:  new row for relation "tbl_check" violates check constraint "ck_tbl_check_b"
DETAIL:  Failing row contains (1, ac). */

Deletion of 3.CHECK constraints

 
/* test=# alter table tbl_check drop constraint ck_tbl_check_a;
ALTER TABLE
test=# insert into tbl_check (a,b) values(-1,‘ab‘);
INSERT 0 1 */

Increase in 4.CHECK constraints

New CHECK constraint must first delete existing data that does not satisfy the constraint

 
/* test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0);
ERROR:  check constraint "ck_tbl_check_a" is violated by some row
test=# delete from tbl_check where a <= 0;
DELETE 1
test=# alter table tbl_check add constraint ck_tbl_check_a check (a > 0);
ALTER TABLE */

PostgreSQL----database table constraints----not null,default,check