Oracle does not support Foreign keys????

From: <wasim_ahmed_at_my-deja.com>
Date: Wed, 08 Sep 1999 05:23:07 GMT
Message-ID: <7r4rrj$plp$1@nnrp1.deja.com>

I am using oracle 8.0.4. I have two tables named WORK_ORDER (child table) and
SCHEDULE_TS_RESOURCE (parent table) which are described below.

SQL> desc work_order;

Name                            Null?    Type
------------------------------- -------- ----
WORK_ORDER_ID                  NOT NULL VARCHAR2(10)
ACCOUNT_NO                      NOT NULL VARCHAR2(10)
CREATED_BY                      NOT NULL VARCHAR2(20)
INITIATION_DATE                NOT NULL DATE
ORDER_TYPE                      NOT NULL CHAR(3)
STATE_ID                        NOT NULL CHAR(3)
STATUS_ID                      NOT NULL CHAR(3)
ASSIGNED_TO                              VARCHAR2(10)
DUE_DATE                                DATE
COMPLETION_DATE                          DATE
COMPLETED_BY                            VARCHAR2(30)
DESCRIPTION                              VARCHAR2(200)
EFFECTIVE_BILL_DATE                      DATE
MISSED_DUE_DATE_REASON_ID                CHAR(3)
TIME_SLOT_NAME                          VARCHAR2(30)
MODIFIED_BY                              VARCHAR2(20)
UNITS                                    NUMBER(4)


SQL> desc schedule_ts_resource;
Name                            Null?    Type
------------------------------- -------- ----
SCHEDULE_DATE                  NOT NULL DATE
TIME_SLOT_NAME                  NOT NULL VARCHAR2(30)
RESOURCE_ID                    NOT NULL VARCHAR2(10)

The primary key of schedule_ts_resource table consists of all columns.

I created the foreign key of table work_order of columns (DUE_DATE, TIME_SLOT_NAME, ASSIGNED_TO) which references the columns (SCHEDULE_DATE, TIME_SLOT_NAME, RESOURCE_ID) of table schedule_ts_resource.
But when I try to insert the values in work_order table which are not present in
schedule_ts_resource table, the values are inserted. I have checked the status
of foreign key constraint and its enabled. I dont know whats happening. Below
are the defination of the constraints in user_constraints and user_cons_columns

SQL> select constraint_name, constraint_type, table_name,

R_constraint_name , status, deferrable, validated, generated, bad from

user_constraints where constraint_name = 'ORD_STR_FK' SQL> /

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
R_CONSTRAINT_NAME              STATUS  DEFERRABLE    VALIDATED
------------------------------ -------- -------------- -------------
GENERATED      BAD


-------------- ---


ORD_STR_FK                    R WORK_ORDER
STR_PK                        ENABLED  NOT DEFERRABLE VALIDATED

USER NAME SQL> select constraint_name, table_name, substr(column_name, 1, 30) from 2 user_cons_columns where constraint_name = 'ORD_STR_FK';

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------

SUBSTR(COLUMN_NAME,1,30)

ORD_STR_FK                    WORK_ORDER
ASSIGNED_TO
ORD_STR_FK                    WORK_ORDER
DUE_DATE
ORD_STR_FK                    WORK_ORDER

TIME_SLOT_NAME SQL> select constraint_name, constraint_type, table_name,

      R_constraint_name
     , status, deferrable, validated, generated, bad from
user_constraints where
     constraint_name = 'STR_PK'


CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
R_CONSTRAINT_NAME              STATUS  DEFERRABLE    VALIDATED
------------------------------ -------- -------------- -------------
GENERATED      BAD


-------------- ---


STR_PK                        P SCHEDULE_TS_RESOURCE
                               ENABLED  NOT DEFERRABLE VALIDATED

USER NAME SQL> select constraint_name, table_name, substr(column_name, 1, 30)

      from
      user_cons_columns where constraint_name = 'STR_PK';

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------

SUBSTR(COLUMN_NAME,1,30)

STR_PK                        SCHEDULE_TS_RESOURCE
SCHEDULE_DATE
STR_PK                        SCHEDULE_TS_RESOURCE
TIME_SLOT_NAME
STR_PK                        SCHEDULE_TS_RESOURCE

RESOURCE_ID I have tried to drop and recreate the constraints (although there is data present
in work_order table which is not present in schedule_ts_resource table) , the constraint is created. Any help or comments are appreciated.
regards,
Wasim.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 08 1999 - 00:23:07 CDT