Showing posts with label NOT NULL Constraint. Show all posts
Showing posts with label NOT NULL Constraint. Show all posts

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.