Re: Error: ORA-02438

OK, I'm embarrased. I forgot the CONSTRAINT keyword.

Brian

On Thu, 28 Sep 2000 16:44:51 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:

>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 - 12:00:18 CDT