Home » SQL & PL/SQL » SQL & PL/SQL » Not null constraint inside CTAS stmt
Not null constraint inside CTAS stmt [message #187136] Fri, 11 August 2006 01:47 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a CTAS statement. After the table was created, half of the columns are 'not null' (not nullable') and some are nullable (can be null). Is it possible to enforce the 'not null' constraint inside the CTAS statement itself? If so, how?? thanks in advance..
Re: Not null constraint inside CTAS stmt [message #187151 is a reply to message #187136] Fri, 11 August 2006 02:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
CREATE TABLE new (
  col1 NOT NULL
, col2     NULL
) AS
SELECT col1, col2
FROM   old


Ross Leishman
Re: Not null constraint inside CTAS stmt [message #187153 is a reply to message #187136] Fri, 11 August 2006 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes you can.
You need to provide a list of the column names and constraints (but not data types) in the CREATE TABLE part of the command.
SQL> create table cons_test (dt_1 not null
  2                         ,dt_2 check (dt_2 > to_Date('01-01-2000','dd-mm-yyyy'))
  3                         ,dt_3) as
  4  select sysdate
  5        ,sysdate
  6        ,sysdate
  7  from dual;

Table created.

SQL> desc cons_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DT_1                                      NOT NULL DATE
 DT_2                                               DATE
 DT_3                                               DATE

SQL> select constraint_name,table_name,search_condition
  2  from user_constraints
  3  where table_name = 'CONS_TEST';

CONSTRAINT_NAME               |TABLE_NAME                    |SEARCH_CONDITION
------------------------------|------------------------------|------------------------------------------------
SYS_C006906                   |CONS_TEST                     |"DT_1" IS NOT NULL
SYS_C006907                   |CONS_TEST                     |dt_2 > to_Date('01-01-2000','dd-mm-yyyy')


[Drat, second]

[Updated on: Fri, 11 August 2006 02:28]

Report message to a moderator

Re: Not null constraint inside CTAS stmt [message #187155 is a reply to message #187153] Fri, 11 August 2006 02:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JRowbottom wrote on Fri, 11 August 2006 17:28

[Drat, second]
That's what you get for providing too thoroughly worked examples Wink
Re: Not null constraint inside CTAS stmt [message #187160 is a reply to message #187155] Fri, 11 August 2006 02:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmph!
Razz
Re: Not null constraint inside CTAS stmt [message #187195 is a reply to message #187160] Fri, 11 August 2006 04:44 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
thanks very much, guys!
Previous Topic: Update table from same table
Next Topic: cursor
Goto Forum:
  


Current Time: Mon Dec 05 07:12:08 CST 2016

Total time taken to generate the page: 0.10236 seconds