foreign key?

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 30 Nov 1999 20:57:21 GMT
Message-ID: <19991130155721.00976.00000242@ng-cg1.aol.com>

A qualified yes...if you want to or can add a unique key to the table being the first column only...see below...

SQL> connect scott/tiger
Connected.
SQL> create table t1
  2 (c1 number(10) not null
  3 ,c2 number(10) not null);

Table created.

SQL> Alter table t1
  2 add CONSTRAINT t1_PK PRIMARY KEY (c1,c2)

  3          USING INDEX PCTFREE 10
  4                      INITRANS 2
  5                      MAXTRANS 255
  6                      TABLESPACE HUMSINDEX
  7                      STORAGE(INITIAL 1K
  8                              NEXT 1K
  9                              MINEXTENTS 1
 10                              MAXEXTENTS UNLIMITED
 11                              PCTINCREASE 0)

 12 ;

Table altered.

SQL> Alter table t1
  2 add CONSTRAINT t1_UK UNIQUE (c1)

  3          USING INDEX PCTFREE 10
  4                      INITRANS 2
  5                      MAXTRANS 255
  6                      TABLESPACE HUMSINDEX
  7                      STORAGE(INITIAL 1K
  8                              NEXT 1K
  9                              MINEXTENTS 1
 10                              MAXEXTENTS UNLIMITED
 11                              PCTINCREASE 0);

Table altered.

SQL> create table t2
  2 (c1 number(10),
  3 c2 number(10));

Table created.

SQL> Alter table t2
  2 add CONSTRAINT t2_PK PRIMARY KEY (c1)

  3          USING INDEX PCTFREE 10
  4                      INITRANS 2
  5                      MAXTRANS 255
  6                      TABLESPACE HUMSINDEX
  7                      STORAGE(INITIAL 1K
  8                              NEXT 1K
  9                              MINEXTENTS 1
 10                              MAXEXTENTS UNLIMITED
 11                              PCTINCREASE 0);

Table altered.

SQL> Alter table t2
  2 add CONSTRAINT t2_t1_FK FOREIGN KEY (c1) REFERENCES t1 (c1);

Table altered.

SQL> insert into t1
  2 values(1,1);

1 row created.

SQL> insert into t2
  2 values(2,1);
insert into t2

            *
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.T2_T1_FK) violated - parent key not found

SQL> insert into t2
  2 values(1,3);

1 row created.

SQL>
Paul in VT Received on Tue Nov 30 1999 - 14:57:21 CST