PostgreSQL 11.2 add constraints, delete constraints, add columns, delete columns

PostgreSQL 11.2 add constraints, delete constraints, add columns, delete columns

Modify the table

Delete constraint [syntax general]
alter table table_name drop constraint “some_name”;

Notes:

1. Which table needs to be operated explicitly

2. Need to know the name of the constraint [may be a primary key constraint, foreign key constraint, check constraint, unique constraint]

3. This is equally valid for all constraint types except non-null constraints

Remove non-empty constraints

alter table table_name alter column ID drop not null;

Note: Since the non-empty constraint has no name, the above method cannot be used.

Add check constraint to the version field in the table

alter table table_name add constraint “table_name_version_check”
check(version between ‘8–1’ and ‘8-z’);

Add unique constraint unique to the field in the table

alter table table_name add constraint “unique_table_name” unique(id);

Increase foreign key constraints

alter table table_name add foreign key (table_name_id) references table_name2(table_name2_id) on update cascade on delete cascade;

Description:

On update cascade: when the referenced row is updated, the referenced row is automatically updated;

on update restrict: the referenced row is prohibited from being updated;

on delete cascade: when the referenced row is deleted, the referenced row is also deleted;

on delete restrict: referenced Is prohibited to delete;

Increase primary key constraints

alter table table_name add primary key(id);

The field in the table adds a non-null constraint

alter table table_name alter column ID set not null;

Note: The constraint will be checked immediately, so the data in the table needs to meet the constraint condition before adding the non-null constraint

Add column

alter table table_name add column name text;

Note:

If the new column does not specify a default value, it will be filled with null

Delete column

alter table table_name drop column name;

Additional note:

If the column is referenced by a foreign key in another table, the delete will fail. Use cascade to authorize the removal of anything that depends on the deleted column.

alter table table_name drop column name cascade;

Change the default value of a column

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Note:

This does not affect the rows that already exist in any table, it just changes the default value for future INSERT commands.

Delete the default value of the column

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

Note:

This is equivalent to setting the default value to null. Correspondingly, trying to delete an undefined default value will not cause an error, because the default value has been implicitly set to a null value

Modify the data type of a column

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

Note: This operation can only succeed if each item in the column can be converted to a new type through an implicit shape.

Rename column

ALTER TABLE products RENAME COLUMN product_no TO product_number;

Rename table

ALTER TABLE products RENAME TO items;

Add constraints when creating a table

Check constraint
Column constraint
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Note: If you do not specify the name CONSTRAINT positive_price can be omitted

Table constraints
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);

Description:

CHECK (price> discounted_price) This constraint is not attached to any column, it is a table-level constraint

Column constraints and table constraints can exist at the same time

Column constraints can be written as table constraints, but not vice versa

Change column constraints to table constraints

CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);

Explanation:

A check constraint is satisfied when its check expression value is true or null. Because most expressions will evaluate to null values ​​when any operand is null, they will not prevent control in constrained columns. To ensure that a column does not contain null values, you can use the non-null constraint not null.

Non-empty constraints

CREATE TABLE products (

product_no integer NOT NULL,

name text NOT NULL,

price numeric NOT NULL CHECK (price > 0)

);

Note: A column can have multiple constraints, the order of constraints is irrelevant.

Unique constraint
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);

Description:

1. You can specify the name of the constraint yourself, CONSTRAINT must_be_different can be omitted, and the system will specify the name after the omission.

2. Adding a unique constraint will automatically create a unique B-tree index on the column or column group listed in the constraint

3. If more than one row in the table has the same value on the columns included in the constraint, the unique constraint will be violated. But in this comparison, the two null values ​​(null) are considered different. Even if there is a unique constraint, you can store multiple rows that contain null values ​​in at least one constrained column.

Table constraints
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);

Note: This writing method is different from the above column constraints, but the effect is the same

CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);

Note: This specifies that the combined value of these columns is unique within the scope of the entire table, but the value of any one of the columns does not need to be unique.

Primary key constraint
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);

Notes:

1. The primary key is unique and not empty

2. The primary key can have multiple columns, and the combined primary key

3. Adding a primary key will automatically create a unique B-tree index on the columns or column groups listed in the primary key. And will force these columns to be marked as NOT NULL

Foreign key constraint

Definition of foreign keys:

A foreign key constraint specifies that the values ​​in one column (or set of columns) must match the values ​​of certain rows that appear in another table. We say that

this maintains the referential integrity between the two related tables.

Examples are as follows:

Product table:
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);

Suppose there is also a table that stores orders for these products. We want to ensure that the order list only contains orders for products that actually exist. So we define a foreign key constraint in the order table that references the product table:

Order form:
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);

It is now impossible to create an order that contains a product_no value (not empty) that does not exist in the product table. We say that in this case, the order table is the reference table and the product table is the referenced table. Correspondingly, there are references to quoted and quoted columns.

Order form shorthand:
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);

Note: If the specified referenced column, the primary key of the referenced table will be used as the referenced column

Thanks for reading this post.