PK, FK Constraints construction

I have the following table structure.

TABLE1
SALARY_DETAILS(N_EMP_ID, D_MONTH ...)
N_EMP_ID NUMBER
D_MONTH DATE

PRIMARY KEY being composite: N_EMP_ID,D_MONTH

TABLE 2
INCOME_TAX(N_EMP_ID,D_YEAR,N_TAX...)

      

I want N_EMP_ID in TABLE2 to be FOREIGN KEY in TABLE1 (N_EMP_ID). Now this gives the following error: -

error

I understand that there is a PK for the specified table for both N_EMP_ID and D_MONTH. Obviously I shouldn't have done below.

ALTER TABLE income_tax ADD FOREIGN KEY (person_id) REFERENCES salary_details(V_EMP_ID,D_MONTH);

      

  • Question 1: What is the correct way / design to fix this?
  • Question 2: Can I have PK and FK constraints in the same column: INCOME_TAX (V_EMP_ID)?

Thanks for your data.

Here is the Create table statement.

CREATE TABLE "ANKUR"."SALARY_DETAILS" 
   (    
    "V_EMP_NAME" VARCHAR2(30 BYTE), 
    "D_MONTH" DATE NOT NULL ENABLE, 
    "V_EMP_ID" VARCHAR2(10 BYTE), 
    "N_NET_PAY" NUMBER(10,2), 
    "N_TOT_EARNINGS" NUMBER(10,2), 
    "N_TOT_DED" NUMBER(10,2), 
    "N_BAS_SAL" NUMBER(10,2), 
    "N_FDA" NUMBER(10,2), 
    "N_HRA" NUMBER(10,2), 
    "N_MED_ALLW" NUMBER(10,2), 
    "N_TRANS_ALLW" NUMBER(10,2), 
    "N_LTA" NUMBER(10,2), 
    "N_BON_EXGRA_ADV" NUMBER(10,2), 
    "N_ANN_BON_EXGRA" NUMBER(10,2), 
    "N_PERF_BON" NUMBER(10,2), 
    "N_LWF" NUMBER(10,2), 
    "N_INCM_TAX" NUMBER(10,2), 
    "N_INFY_WELTRUST" NUMBER(10,2), 
    "N_MLPL" NUMBER(10,2), 
    "N_LIB_DEDUC" NUMBER(10,2), 
    "N_PF" NUMBER(10,2), 
     CONSTRAINT "PK_PERSON" PRIMARY KEY ("V_EMP_ID", "D_MONTH")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

      

+3


source to share


1 answer


EMPLOYEES must be a separate table referenced by both SALARY_DETAILS and INCOME_TAX. This way you can eliminate redundancy as well as eliminate your FK problems.



+4


source







All Articles