like foreign key how to refer another column value in same table for values using oracle?

Question

Possible Duplicate:
How to enforce a constraint of foreign key on columns of same table in SQL

Table1

Column1 Column2  Column3 
 1        value  1
 2        value  1
 3        value  2

here in a same table column 1 is primary key, but column3 value must be from column1. How to create table like this.

Robert · Answer 1 · 2012-11-22T09:59:34.827

2

Probably you looking for self referencing table

create TABLE MyTable
(
   Column1 NUMBER,
   Column2 varchar2(30),
   Column3 NUMBER,
   PRIMARY KEY(column1),
   CONSTRAINT fk_column1_2
    FOREIGN KEY (column3)
    REFERENCES MyTable (column1)

);

edited Nov 22 '12 at 09:59

answered Nov 22 '12 at 09:53

Robert

25,425
8
67
81

you rejected the edit , then it wont work for oracle .... – sunleo Nov 22 '12 at 09:59
of course not because there two mistakes , there is no keyword "table", int won't work for oracle. – sunleo Nov 22 '12 at 10:03
@sunleo Now I see your changes, sorry. I edited the post. – Robert Nov 22 '12 at 10:04

score 2 · Accepted Answer · answered Nov 22 '12 at 09:57

If your table is already there then you need to run couple of ddl statements

CREATE TABLE TABLE_1 
    ( 
     column1 NUMBER (4)  NOT NULL , 
     column2 VARCHAR2(20)  NOT NULL , 
     column3 NUMBER (4) 
    ) 
;


ALTER TABLE TABLE_1 
    ADD CONSTRAINT TABLE_1_PK PRIMARY KEY ( column1) ;



ALTER TABLE TABLE_1 
    ADD CONSTRAINT TABLE_1_TABLE_1_FK FOREIGN KEY 
    ( 

     column3
    ) 
    REFERENCES TABLE_1 
    ( 
     column1
    ) 
;

score 0 · Answer 3 · answered Nov 22 '12 at 09:57

0

You could do this with a CHECK Constraint :

CREATE TABLE TEST (
 a number,
 b number,
 CONSTRAINT CKAEQUALB CHECK (a = b)
);

answered Nov 22 '12 at 09:57

BaNz

202
2
16

it wont work for final case. – sunleo Nov 22 '12 at 10:00

like foreign key how to refer another column value in same table for values using oracle?

3 Answers3