Error: ORA-02438

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Thu, 28 Sep 2000 16:44:51 GMT
Message-ID: <39d3733d.89113375@news.alt.net>

I tried to create a table:

CREATE TABLE MyTable(

 A_Id	NUMBER(8),
 B_Id	NUMBER(8),
 Opt_Date	Date DEFAULT(SYSDate),
 MT1_A_Id_NOT_NULL	          CHECK (A_Id IS NOT NULL),
 MT1_B_Id_NOT_NULL           CHECK (B_Id IS NOT NULL),
 MT1_Opt_Date_NOT_NULL   CHECK (Opt_Date IS NOT NULL),
 MT1_A_Id_FK   	FOREIGN KEY (A_Id) REFERENCES  A(Id),
 MT1_B_Id_FK                 FOREIGN KEY (B_Id) REFERENCES B(Id),
 MT1_A_Id_B_Id_PK	PRIMARY KEY (A_Id, B_Id)

);

When I created this table I got the following error. (Asterisk is not in right place):

  2 3 4 5 6 7 8 9 10 11   MT1_A_Id_NOT_NULL CHECK (A_Id IS NOT NULL),

     *
ERROR at line 5:
ORA-02438: Column check constraint cannot reference other columns

However, I then tried created the table with just columns and add the constraints with alter table statements, and it was created!

CREATE TABLE MyTable(

 A_Id	NUMBER(8),
 B_Id	NUMBER(8),
 Opt_Date	Date DEFAULT(SYSDate),

);

ALTER TABLE MyTable Add CONSTRAINT
  MT1_A_Id_NOT_NULL CHECK (A_Id IS NOT NULL); ALTER TABLE MyTable Add CONSTRAINT
 MT1_B_Id_NOT_NULL CHECK (B_Id IS NOT NULL); ALTER TABLE MyTable Add CONSTRAINT
 MT1_Opt_Date_NOT_NULL CHECK (Opt_Date IS NOT NULL); ALTER TABLE MyTable Add CONSTRAINT
 MT1_A_Id_FK FOREIGN KEY (A_Id) REFERENCES A(Id); ALTER TABLE MyTable Add CONSTRAINT

 MT1_B_Id_FK                 FOREIGN KEY (B_Id) REFERENCES B(Id);
ALTER TABLE MyTable Add CONSTRAINT
 MT1_A_Id_B_Id_PK	PRIMARY KEY (A_Id, B_Id);

What was the original error?

Brian Received on Thu Sep 28 2000 - 11:44:51 CDT