Thursday, June 16, 2011

CTAS (Create Table As Select)and NOT NULL Constraints

Stumbled across YAOF (Yet Another Oracle Feature) today; When performing a CTAS (Create Table As Select) NOT NULL Constraints are usually created, for example:
CREATE TABLE ctas_test
( id NUMBER NOT NULL
, name VARCHAR2(30) NULL );

CREATE TABLE ctas_test_copy AS
SELECT * FROM ctas_test;

SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'CTAS_TEST%'
ORDER BY table_name, column_name;

As you can see the Not Null Constraints have been successfully replicated to the CTAS_TEST_COPY table. However if I now execute:

CREATE TABLE ctas_test_minus AS
SELECT * FROM ctas_test
MINUS
SELECT * FROM ctas_test;

SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE table_name LIKE 'CTAS_TEST%'
ORDER BY table_name, column_name;

The Not Null Constraints are missing from the CTAS_TEST_MINUS table.

1 comment:

Syed said...

Great, comes handy, Thanks!