ALTER TABLE INHERIT

Function

Modify the inheritance table, including changing the regular table to an inheritance table and changing the inheritance table to a regular table.

Precautions

• Only tables that fully contain the parent table structure can be changed to child tables.
• After terminating the inheritance relationship, although it is no longer a child table, there are still columns with the same name and type inherited from the parent table, and the existing data will not be deleted.
• Modify the table structure of the parent table, and inherit the table accordingly.
• Modify the data of the parent table, and the data of the inherited table will be updated together.
• The not null, default, and check constraints inherited from the parent table cannot be deleted or modified.
• The parent table deletes a column, the child table's column will not be deleted when using like parent_name clause to create a table.
• The parent table deletes a column, the child table columns will be deleted when not using like parent_name clause to create a table.
• Indexes, uniqueness, primary keys, and foreign key constraints which are using include all to inherit from the parent table can be deleted or modified.

Syntax

ALTER TABLE table_name { inherit | no inherit } parent_name;

• please refer to alter table chapter to get more detailed parameter explanations.

Parameter Description

• table_name

  Specifies the name of the child table.

  Value range: an existing partitioned table name.

• parent_name

  Specifies the name of parent table to inherit.

  Value range: an existing partition name.

Examples

--Create two parent tables
openGauss=# CREATE TABLE father
(
    id int NOT NULL,
    md_attr CHARACTER VARYING(32) UNIQUE,
    num int DEFAULT 2,
    salary REAL CHECK(SALARY > 0),
    CONSTRAINT pk_father_z83rgvsefn PRIMARY KEY (id)
);
openGauss=# CREATE TABLE father2 (id int);

--Create child tables
openGauss=# CREATE TABLE child (id int);
openGauss=# ALTER TABLE child inherit father2;
openGauss=# CREATE TABLE child2() inherits(father);
openGauss=# CREATE TABLE child3(like father) inherits(father);

--Modify the table structure of the parent table, and the child tables will follow the changes.
openGauss=# ALTER TABLE father alter COLUMN id type CHAR;

--Parent tables drop column
openGauss=# ALTER TABLE father DROP COLUMN if exists salary;

--When not using like parent_name to create child table, the child table columns will be deleted
openGauss=# \d+ child2

--When using like parent_name to create child table, the child table columns will not be deleted
openGauss=# \d+ child3

--Termination of inheritance relationship
openGauss=# ALTER TABLE child no inherit father2;

--drop tables
openGauss=# drop table father cascade;
openGauss=# drop table child cascade;
openGauss=# drop table father2 cascade;

CREATE TABLE INHERITS.

Helpful Links

CREATE TABLE INHERITS and DROP TABLE