WITH OPTIONS for Table Cloning
The WITH options are optional, and appear after the destination-source pairs. If more than one destination-source pair is given, the WITH options apply to all of them.
WITH options can be combined, for example:
WITH REFERENCES=NONE, GRANTS
WITH REFERENCES=NONE
If WITH REFERENCES=NONE option is specified, then the foreign key constraints are not copied from the existing tables to the new tables. This option is useful to clone one or a subset of a larger group of tables referentially connected to each other, and if you want to clone only the table's rows and not its relationships to any other tables.
The default is WITH REFERENCES=RESTRICTED if no REFERENCES option is specified. Any referential relationships between the named existing tables will be replicated between the corresponding new tables by creating foreign keys in the new tables.
WITH REFERENCES =RESTRICTED or REFERENCES=EXTENDED
The WITH REFERENCES=EXTENDED option creates references between the new tables as REFERENCES=RESTRICTED above, and in addition, any foreign key references to tables which are not being cloned in the clone operation. The user must have permission to reference those existing tables.
For more information, see the table below in Foreign Keys in Cloned Tables.
WITH GRANTS or WITH NOGRANTS
If WITH GRANTS option is specified, the privileges users have on the existing table are also granted on the new table. The new table is always owned by the user who created it, not the owner of the source table.
Note: If the user is not the owner of the source table, beware that the WITH GRANTS option does not grant to the source table’s owner any implied permissions they had on the source table by owning it. Only the privileges granted on the existing table are copied to the new table. If the source table’s owner’s implied permissions are desired on the new table, they must be granted explicitly.
The default is WITH NOGRANTS, which means privileges are not copied, and the new tables will be readable and writable by their creator as if created by an ordinary CREATE TABLE statement.
WITH DATA or WITH NODATA
If WITH NODATA option is specified, new tables are created with the same definition as the existing table(s) but the new tables are left empty.
Default is WITH DATA. WITH DATA cannot be used with Ingres tables, therefore an explicit "WITH NODATA" clause is needed when at least one heap table is cloned.
Referential Integrity Constraints in Cloned Tables
Any primary or unique key constraints on the source tables are copied and created on the destination tables.
Unless WITH REFERENCES=NONE is used, any foreign key constraints are also created on the destination tables as they are on the source tables.
Foreign Keys in Cloned Tables
Foreign key relationships between the source tables are cloned in the destination tables unless WITHREFERENCES=NONE is used.
For example, if table C has a foreign key which references table P, and both the tables are cloned in the same statement:
CREATE TABLE NEWC CLONE C, NEWP CLONE P
An identical foreign key reference will be created between NEWC and NEWP. The two new tables will have no relationship to either C or P.
By default, foreign key relationships are created only between tables created by the clone operation. The clone operation does not create foreign key references to existing tables unless WITH REFERENCES=EXTENDED is used and the source table having primary key is not cloned in the same statement.
For example, in the above case, where table C has a foreign key which references table P, if we only clone C:
CREATE TABLE NEWC CLONE C
then NEWC is not given a foreign key reference to P.
Example:
To describe this further, let us assume that there are three tables X, Y and Z, referentially related as follows:
• X has a primary key
• Y has a primary key, and a foreign key referencing X
• Z has a foreign key referencing Y.
Run the SQL CREATE TABLE Y_NEW CLONE Y, Z_NEW CLONE Z with various options and the results are displayed in the illustration below:
Restrictions
Cloning a table has the following restrictions:
• Only X100 tables may be cloned.
• On Actian X, the CLONE syntax may be used on Ingres tables but only to make a metadata copy of a table or set of tables. Ingres table data cannot be cloned with data. Therefore, when used on Ingres tables, WITH NODATA is mandatory.
• Metadata cloning of Ingres tables on Actian X is not supported for partitioned Ingres tables.
• Views, external tables and system catalogs may not be cloned.
• Tables may only be cloned within the same database, not between two different databases.
• Tables containing sequence defaults may not be cloned.
• To protect the security of masked columns, the user may not clone a table that has masked columns unless they could remove the mask (i.e. they own the table or have the UNMASK privilege).
Cloning a table in violation of any one of the restrictions results in an error.
Last modified date: 08/14/2024