Home » RDBMS Server » Server Administration » URGENT:-Constraint problem
| URGENT:-Constraint problem [message #142374] |
Fri, 14 October 2005 07:06  |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hi Gurus,
I have two tables TREP_SERVICE_TYPE and TREPRESENTATION.
Giving you the DDL to understand the constraints in a better way:-
TREP_SERVICE_TYPE:-
CREATE TABLE "ARROW"."TREP_SERVICE_TYPE" ("SERVICE_TYPE_ID"
NUMBER(9) NOT NULL, "SERVICE_NAME" VARCHAR2(60 byte) NOT NULL,
"SERVICE_CLASS" CHAR(1 byte) NOT NULL, "AVAILABLE_IN" CHAR(1
byte) NOT NULL, "CAN_CREATE" CHAR(1 byte) NOT NULL,
"CAN_EDIT" CHAR(1 byte) NOT NULL, "DOM_OR_FGN" CHAR(1 byte),
"JURIS_ID" NUMBER(9), "PARENT_ID" NUMBER(9), "ENTITY_TYPE_CD"
NUMBER(9), CONSTRAINT "JURIS_REPSVCTYP_FK" FOREIGN KEY "JURIS_ID") REFERENCES "GDS"."TJURISDICTION_KEYS"("JURISDICTION_ID"),
CONSTRAINT "REPSVCTYP_REPSVCTYP_FK" FOREIGN KEY("PARENT_ID")
REFERENCES "ARROW"."TREP_SERVICE_TYPE"("SERVICE_TYPE_ID"),
CONSTRAINT "REPTYPE_ENTITYTYPE_CHK" CHECK(entity_type_cd
between 1001 and 1999),
CONSTRAINT "REP_SERVICE_TYPE_PK" PRIMARY
KEY("SERVICE_TYPE_ID")
USING INDEX
TABLESPACE "INDEXSPACE"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255
NOVALIDATE)
TABLESPACE "DATASPACE" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING ;
TREPRENTATION:-
CREATE TABLE "ARROW"."TREPRESENTATION" ("REP_ID" NUMBER(18) NOT
NULL, "ENTITY_ID" NUMBER(18) NOT NULL, "JURIS_ID" NUMBER(9)
NOT NULL, "SERVICE_TYPE_ID" NUMBER(9) NOT NULL, "AGENT_ID"
NUMBER(9), "STATE_IDENTIFIER" VARCHAR2(25 byte),
"CURRENT_STATUS_CD" NUMBER(9) NOT NULL, "UNIT_COUNT"
NUMBER(9), "ANNUAL_RATE" NUMBER(9, 2), "EXPIRATION_DATE" DATE,
"SERVICE_DESCRIPTION" VARCHAR2(855 byte),
"ORIGINAL_FILING_DATE" DATE, "CT_APPOINTMENT_DATE" DATE,
"SERVICE_START_DATE" DATE, "CREATED_BY" VARCHAR2(25 byte) NOT
NULL, "CREATED_DATE" DATE DEFAULT sysdate NOT NULL,
"LAST_MODIFIED_BY" VARCHAR2(25 byte) NOT NULL,
"LAST_MODIFIED_DATE" DATE DEFAULT sysdate NOT NULL,
CONSTRAINT "ENT_REP_FK" FOREIGN KEY("ENTITY_ID")
REFERENCES "ARROW"."TENTITY"("ENTITY_ID"),
CONSTRAINT "JURIS_REP_FK" FOREIGN KEY("JURIS_ID")
REFERENCES "GDS"."TJURISDICTION_KEYS"("JURISDICTION_ID"),
CONSTRAINT "LKUP_REP_AGENTSTATUS_FK" FOREIGN
KEY("CURRENT_STATUS_CD")
REFERENCES "ARROW"."TLOOKUP"("LOOKUP_CD"),
CONSTRAINT "REPRESENTATION_PK" PRIMARY KEY("REP_ID")
USING INDEX
TABLESPACE "INDEXSPACE"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255,
CONSTRAINT "REPSVCTYP_REP_SERVICETYPE_FK" FOREIGN
KEY("SERVICE_TYPE_ID")
REFERENCES "ARROW"."TREP_SERVICE_TYPE"("SERVICE_TYPE_ID"),
CONSTRAINT "REP_AGENT_FK" FOREIGN KEY("AGENT_ID")
REFERENCES "ARROW"."TAGENT"("AGENT_ID"),
CONSTRAINT "REP_CURRENTSTATUS_CHK" CHECK(current_status_cd
between 6001 and 6999),
CONSTRAINT "REP_STATEID_CHK" CHECK( trim(state_identifier) <>
'' ), CONSTRAINT "REP_SVCDESC_CHK" CHECK( trim service_description)<> '' ))
TABLESPACE "DATASPACE" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
147483645 PCTINCREASE 0)
LOGGING ;
Now, my problem is that I want to insert two identical values for Service_type_id column of TREP_SERVICE_TYPE table. But, as it is a primary key this will not allow me to enter duplicate value. I tried with making the constraint composite with JURIS_ID column so that I can enter duplicate value but then it gives problem with the foreign key constraint on TREPRESENTATION table. I need a solution for this so that I can insert duplicate values for SERVICE_TYPE_ID column of TREP_SERVICE_TYPE table without deleting the constraints.I can modify the constraints but not drop them. So, please suggest me the steps so that I can achieve this without disturbing the relationship between both the tables.
Please suggest the solution as soon as possible as this is urgent.
Thanks in advance.
Milind.
|
|
|
|
| Re: URGENT:-Constraint problem [message #142402 is a reply to message #142374] |
Fri, 14 October 2005 08:24  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
I think you are trying to temper the table design related to application ,,, or ur table design is worng .. or u had not done proper normalization of table as per your requirement... Frankly speaking you dont have to change any thing in ur table design if your application was working fine earlier but if you r developing application now then ... u can change it but u have to re structure the tables as per your requirement.
you know when u make composite primary key you get error of foregin key on another table TREPRESENTATION table because this composite primary kye must be foregin in table.
From may explanation I Hope u will get some idea
Regard
Always friend sunil
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 11:24:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
