Re: New Student SQLPLUS Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 14 Oct 2001 15:42:15 -0700
Message-ID: <9qd4c70j1o@drn.newsguy.com>

In article <iUmy7.18899$n5.3657596_at_typhoon.nyc.rr.com>, "Yuval says...
>
>Hi I have a basic easy question. I am learning how to create tables and am
>getting this error when I proform the CREATE TABLE command. Any Help?
>
>SQL> CREATE table Ordr_Data
> 2 (
> 3 Order_id NUMBER(10) CONSTRAINT pk_order_data_order_id PRIMARY KEY,
> 4 Cust_id NUMBER(5) NOT NULL,
> 5 Sales_rep_id NUMBER(5) NOT NULL,
> 6 Prod_id NUMBER(10) NOT NULL CONSTRAINT uq_ordr_data_prod_id UNIQUE,
> 7 Quantity NUMBER CONSTRAINT ck_quantity CHECK(Quantity <= 1000),
> 8 Order_date DATE NOT NULL,
> 9 Order_Stat VARCHAR2(10) NOT NULL,
> 10 Ship_Date DATE NOT NULL,
> 11 Org_User_id NUMBER(5) NOT NULL,
> 12 Org_User_Date DATE NOT NULL,
> 13 Upd_User_id NUMBER(5),
> 14 Upd_User_Date DATE,
> 15 CONSTRAINT uq_Ordr_Data_Custid_ordrdt UNIQUE (cust_id,Order_date),
> 16 ck_Ordr_data_order_date CHECK (Order_date > '01-JAN-01'));
>ck_Ordr_data_order_date CHECK (Order_date > '01-JAN-01'))
> *
>ERROR at line 16:
>ORA-02438: Column check constraint cannot reference other columns
>
>

You are missing the keyword constraint on line 16, like I am on line 6:

ops$tkyte_at_ORA8I.WORLD> create table t
  2 ( x int,
  3 y date,
  4 constraint x_check check ( x > 0 ),   5 y_check check ( y > '01-JAN-01' )   6 )
  7 /
)
*
ERROR at line 6:
ORA-02438: Column check constraint cannot reference other columns

but even then, you will fail:

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create table t
  2 ( x int,
  3 y date,
  4 constraint x_check check ( x > 0 ),   5 constraint y_check check ( y > '01-JAN-01' )   6 )
  7 /
  constraint y_check check ( y > '01-JAN-01' )

ERROR at line 5:
ORA-02436: date or system variable wrongly specified in CHECK constraint

You need to to_date that and you NEED to use 4 digits for the year (we should all know better by now on that one!)

ops$tkyte_at_ORA8I.WORLD> create table t
  2 ( x int,
  3 y date,
  4 constraint x_check check ( x > 0 ),   5 constraint y_check check ( y > to_DATE( '01-JAN-2001' , 'dd-mon-yyyy' ))   6 )
  7 /

Table created.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sun Oct 14 2001 - 17:42:15 CDT