1,105,320 Community Members

Oracle Foreign Key problem

Member Avatar
begueradj
Junior Poster in Training
93 posts since Mar 2007
Reputation Points: -1 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hello people,

I want to create 'Voyageur' table that references a successfully created table which name is 'Categorie',.

But I get this error message when I click on 'run':
ORA-02438: Column check constraint cannot reference other columns

Here is my 'Voyageur' Table that makes me a problem around its foreign key:

CREATE TABLE Voyageur(
numV NUMBER(4) NOT NULL PRIMARY KEY CONSTRAINT check_numV
     CHECK(numV LIKE'[!a-zA-Z]') DISABLE,
nomV VARCHAR(15) NOT NULL CONSTRAINT check_nomV
     CHECK(nomV LIKE'[!0-9]') DISABLE,
prenomV VARCHAR(15) NOT NULL CONSTRAINT check_prenomV
     CHECK(prenomV LIKE'[!0-9]')DISABLE,
birthdayV DATE NOT NULL DISABLE,
addressV VARCHAR(50) NOT NULL DISABLE,
telV VARCHAR(20) NOT NULL CONSTRAINT check_telV
     CHECK(telV LIKE'[+]%' AND telM LIKE'[!a-zA-Z]') DISABLE,
idCat NUMBER(1) NOT NULL DISABLE,
FOREIGN KEY(idCat) REFERENCES Categorie(idCat)
);

And here is my 'Categorie' table that works fine:

CREATE TABLE Categorie(
idCat NUMBER(1) NOT NULL DISABLE,
nomCat VARCHAR(20) NOT NULL CONSTRAINT check_nomCat
       CHECK(nomCat IN('insulaire','normale','insulairesecondaire'))  DISABLE,
reduction  NUMBER(3) NOT NULL  CONSTRAINT check_reduction
          CHECK(reduction IN ( '25' , '50' , '100' )) DISABLE,
PRIMARY KEY(idCat)
);

Any one can help me to resolve this trouble ?
Thank you very much.

Member Avatar
debasisdas
Posting Genius
6,542 posts since Feb 2007
Reputation Points: 580 [?]
Q&As Helped to Solve: 476 [?]
Skill Endorsements: 25 [?]
Featured
 
0
 

You need to define the constraint at table level not at column level.

That will work.

Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

Your Check Constraint check_telV is referencing a Column telM, which doesn't exist in your Table Voyageur.

ps. What's with all of the DISABLEs?

Member Avatar
begueradj
Junior Poster in Training
93 posts since Mar 2007
Reputation Points: -1 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

You need to define the constraint at table level not at column level.

That will work.

Please, tell me how I can do that ?

Member Avatar
begueradj
Junior Poster in Training
93 posts since Mar 2007
Reputation Points: -1 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Please, tell me how I can do that ?

I have already declared it as a table constraint but it does not work (from the start, as the code shows it). Any other suggestion ? Thank you

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: