Re: foreign key?

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 30 Nov 1999 20:51:24 GMT
Message-ID: <19991130155124.00976.00000239@ng-cg1.aol.com>

FOREIGN KEY (Referential) Integrity Constraints Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved.

Several terms are associated with referential integrity constraints:

foreign key
 The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key (see the following).  

referenced key
 The unique key or primary key of the same or different table that is referenced by a foreign key.  

dependent or child table
 The table that includes the foreign key. Therefore, it is the table that is dependent on the values present in the referenced unique or primary key.  

referenced or parent table
 The table that is referenced by the child table's foreign key. It is this table's referenced key that determines whether specific inserts or updates are allowed in the child table.    

A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.

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:51:24 CST