Как установить значение по умолчанию для столбца новой созданной таблицы из инструкции select в 11g

Я создаю таблицу в Oracle 11g со значением по умолчанию для одного из столбцов. Синтаксис:

create table xyz(emp number,ename varchar2(100),salary number default 0);

Это успешно создано. По некоторым причинам мне нужно создать другую таблицу с той же старой структурой таблицы и данными. Поэтому я создал новую таблицу с именем abc как

create table abc as select * from xyz.

Здесь "abc" успешно создан с той же структурой и данными, что и старая таблица xyz. Но для столбца "зарплата" в старой таблице значение "xyz" по умолчанию было установлено на "0". Но во вновь созданной таблице "abc" значение по умолчанию не установлено.

Это все в Oracle 11g. Скажите, пожалуйста, почему значение по умолчанию не было установлено и как мы можем установить это, используя инструкцию select.

Ответ 1

Вы можете указать ограничения и значения по умолчанию в CREATE TABLE AS SELECT, но синтаксис выглядит следующим образом

create table t1 (id number default 1 not null);
insert into t1 (id) values (2);

create table t2 (id default 1 not null)
as select * from t1;

То есть, он не наследует ограничений из исходной таблицы /select. Только тип данных (длина/точность/масштаб) определяется выбором.

Ответ 2

Причина в том, что CTAS (Create table as select) не копирует метаданные из источника в целевую таблицу, а именно

нет первичного ключа
никаких внешних ключей
нет грантов
нет индексов
...

Чтобы достичь того, чего вы хотите, я бы либо

используйте dbms_metadata.get_ddl, чтобы получить полную структуру таблицы, замените имя таблицы на новое имя, выполните это выражение и затем сделайте INSERT для копирования данных.
или продолжайте использовать CTAS, извлеките ненулевые ограничения для исходной таблицы из user_constraints и затем добавьте их в целевую таблицу.

Ответ 3

Вам нужно alter table abc modify (salary default 0);

Ответ 4

Новая таблица наследует только ограничение "не null" и никакое другое ограничение. Таким образом, вы можете изменить таблицу после ее создания командой "create table as" или вы можете определить все ограничения, которые вам нужны, следуя

create table t1 (id number default 1 not null);
insert into t1 (id) values (2);

create table t2 as select * from t1;

Это приведет к созданию таблицы t2 с ненулевым ограничением. Но для некоторых других ограничений, кроме "не null", вы должны использовать следующий синтаксис

create table t1 (id number default 1 unique);
insert into t1 (id) values (2);

create table t2 (id default 1 unique)
as select * from t1;