CREATE TABLE AS SELECT : Foreign Key pas reprise?

**Ikebukuro** · 13/02/2017, 16h53

Hello les forumeurs,

J'ai créé une table avec des contraintes PK, UNIQUE, FK, CHECK et NOT NULL.
En faisant un CTAS (CREATE TABLE AS SELECT), je constate que les contraintes PK, UNIQUE et FK ne sont pas reprises. Seules sont reprises les contraintes CHECK et NOT NULL (sauf celle sur la PK).

En parcourant le net, il est dit que pour les contraintes PK et UNIQUE, c'est pour éviter d'avoir à créer automatiquement un index alors que le user n'en a peut-être pas besoin sur sa nouvelle table.
OK, mais il n'est rien dit sur l'absence de la reprise de la contrainte Foreign Key, savez-vous pourquoi?

Voici mon test.
Création de la table avec les contraintes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SQL> CREATE TABLE TEST01 (
          ID NUMBER(5) CONSTRAINT PK_T01_ID PRIMARY KEY, 
          NOM VARCHAR2(50 CHAR) CONSTRAINT U_T01_NOM UNIQUE, 
          PRENOM VARCHAR2(50 CHAR) CONSTRAINT NN_PRENOM NOT NULL, 
          T_DEPTNO NUMBER(2) CONSTRAINT FK_T01_DEPT REFERENCES DEPT(DEPTNO), 
          HIRE_DATE DATE, CONSTRAINT CK_T01_HIREDATE CHECK(HIRE_DATE IS NOT NULL));
Table created.

CTAS sur la table TEST01

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TABLE TEST02 AS SELECT * FROM TEST01;

Un desc montre que la contrainte NOT NULL sur la colonne PRENOM a bien été conservée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> desc test01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(5)
 NOM                                                VARCHAR2(50 CHAR)
 PRENOM                                    NOT NULL VARCHAR2(50 CHAR)
 T_DEPTNO                                           NUMBER(2)
 HIRE_DATE                                          DATE
 
SQL> desc test02
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(5)
 NOM                                                VARCHAR2(50 CHAR)
 PRENOM                                    NOT NULL VARCHAR2(50 CHAR)
 T_DEPTNO                                           NUMBER(2)
 HIRE_DATE                                          DATE

En revanche, dans USER_CONSTRAINTS, seule la contrainte CHECK sur HIREDATE a été reprise pour la table TEST02 : plus de PK, de UNIQUE, de FK...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SQL> select table_name, constraint_name, constraint_type from user_constraints where table_name like 'TEST%' order by table_name, constraint_name;
 
TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
TEST01                         CK_T01_HIREDATE                C
TEST01                         FK_T01_DEPT                    R
TEST01                         NN_PRENOM                      C
TEST01                         PK_T01_ID                      P
TEST01                         U_T01_NOM                      U
TEST02                         SYS_C008585                    C
6 rows selected.

**Waldar** · 13/02/2017, 17h48

CTAS n'est pas une duplication de table, il est exactement ce qu'il dit être : "matérialise-moi ce select dans une table".

Ce select pouvant être n'importe quoi, ça deviendrait rapidement ingérable d'aller vérifier toutes les contraintes sous-jacentes.

**Ikebukuro** · 13/02/2017, 18h37

Je ne suis pas d'accord, ce que je ne comprends pas c'est pourquoi certaines contraintes sont reprises (NOT NULL et CHECK) et pas FOREIGN KEY.
C'est pourquoi FOREIGN KEY est absent qui me trouble, quelle est la raison derrière cela? Dans mon post j'explique pourquoi PK et UNIQUE ne sont pas dupliquées mais FK... pas d'info!

**mnitu** · 13/02/2017, 20h54

Ni les contraintes de type check ne sont pas repris c'est juste le not null.

Oracle Database derives data types and lengths from the subquery. Oracle Database follows the following rules for integrity constraints and other column and table attributes:

Oracle Database automatically defines on columns in the new table any NOT NULL constraints that have a state of NOT DEFERRABLE and VALIDATE, and were explicitly created on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column. If any rows violate the constraint, then the database does not create the table and returns an error.

NOT NULL constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.

In addition, primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column default values are not carried over to the new table.

If the selected table is partitioned, then you can choose whether the new table will be partitioned the same way, partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired partitioning as part of the CREATE TABLE statement before the AS subquery clause.

A column that is encrypted using Transparent Data Encryption in the selected table will not be encrypted in the new table unless you define the column in the new table as encrypted at create time.

Je m'imagine que cela tiens plus à l'historique d'Oracle quoi que j'aimerais bien que SQL*Pro nous dit deux mots sur le sujet.

Du point de vue pratique si le CTAS ne vous suffit pas vous devez utiliser la package DBMS_METADATA pour vos besoins. Encore une preuve qu'on sait faire mais que ce n'est pas simple via CTAS.

**Ikebukuro** · 13/02/2017, 22h46

Merci pour ta remarque mnitu!
Je croyais que la contrainte CHECK était reprise dans la nouvelle table mais c'est la contrainte NOT NULL en fait. Là où je me suis fait avoir c'est que NOT NULL est considéré comme une contrainte CHECK par Oracle, on le voit bien pour la colonne "PRENOM VARCHAR2(50 CHAR) CONSTRAINT NN_PRENOM NOT NULL" car dans USER_CONSTRAINTS la contrainte est de type C, comme les CHECK.

J'aurai appris quelque chose, merci!

**mnitu** · 14/02/2017, 17h51

En fait le Not Null existait déjà quand Oracle à décidé d'implémenter les contraintes de type check (version 6.0 je pense) Après si je ne m'abuse pas il y a eu une période pendant laquelle not null n'était pas assimilé à une contrainte check de type not null !
Comme je l'ai dit c'est historique avant tout et non pas justifié par une quelconque impossibilité logique ou physique.

**Ikebukuro** · 14/02/2017, 19h07

Merci pour la précision mnitu, Oracle traîne encore aujourd'hui un historique sur plein de points différents, qui ne sont pas forcément évidents mais c'est ainsi, il faut faire avec.

**pachot** · 18/02/2017, 20h06

Bonjour,
Il n'y a aucune raison de reprendre des méta-données des tables lues lors d'un CTAS. La source n'est pas une table, mais un SELECT qui peut lire plusieurs tables, les transformer, etc.
Le seules méta-données qui sont reprises sont celles du curseur du select: types de donnée, précision, nullable... Tout ce quon trouve dans: https://docs.oracle.com/database/121...htm#TTPLP71280 et rien de plus.
Cordialement,
Franck.

**Ikebukuro** · 20/02/2017, 10h23

Salut Franck,
Excellente remarque de ta part : "La source n'est pas une table, mais un SELECT".

Effectivement, on crée une nouvelle table non pas à partir d'une ancienne table mais à partir d'un SELECT... j'aurais encore appris quelque chose ici :-)

**Waldar** · 20/02/2017, 10h45

Je vous ai dit la même chose la semaine passée !!!
En vrai ce n'est pas grave l'important c'est que vous ayez compris.

**Ikebukuro** · 20/02/2017, 12h00

Oui, c'est vrai mais là on enfonce encore plus le clou!
En tout cas merci pour les réponses.

**GoLDoZ** · 09/03/2017, 15h52

Bonjour.

Je rebondis sur le sujet car je souhaite dupliquer une table en modifiant certains types de colonnes. Vu qu'il y a 168 colonnes, je souhaite utiliser le CTAS. Problème: la table est partitionnée. Je dois donc créer les partitions "à la main".

Il me semble avoir la bonne syntaxe mais cela ne fonctionne pas:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE new_table PARTITION BY RANGE (date_chargement)
(
PARTITION "P1" VALUE LESS THAN (TO_DATE......),
PARTITION "P2" VALUE LESS THAN (TO_DATE......),
....
PARTITION "PN" VALUE LESS THAN (TO_DATE......)
)
AS SELECT ... FROM old_table;
 
Erreur SQL : ORA-00926: mot-clé VALUES absent