CODEWITHSUNDEEP
databaseoracle-databaseplsql
FutoRicky
FutoRicky

Reputation: 1043

How to inherit column value from another table

I am trying to create a table which has columns that should inherit the value from another table, but the problem is that those values are not unique (not a primary key from the other table). How am I able to achieve this?

I was trying to set it like a foreign key but that cannot be done since the value is nos a primary key.

I want fldDiscountPercent and fldChargeType from the table tblOrderCharges inherited from another table (tblPartsServices). How would I achieve this?

CREATE TABLE tblPartsServices(
    fldPartServiceId        VARCHAR2(10) NOT NULL,
    fldPartServiceDescription           VARCHAR2(255),
    fldPartServiceType  CHAR(1)  NOT NULL,          -- P for Part, S for Service, O for Other
    fldPartServiceAmount    NUMBER(8,2) NOT NULL,
    fldDiscountPercent NUMBER(2) ,
    fldStatus CHAR(1) NOT NULL,             -- A for Active, I for Inactive
    CONSTRAINT PartService_pk PRIMARY KEY (fldPartServiceId)
);

CREATE TABLE tblOrderCharges(
    fldChargeId NUMBER(10) NOT NULL,
    fldChargeReference VARCHAR2(10) NOT NULL,    --  Part number or service number (Could be inherited from PartsServices table)
    fldChargeCode VARCHAR2(10) ,            --  Part number or service number (Could be inherited from PartsServices table)
    fldChargeType CHAR(1),                                   --  Type: P for Part, S for Service, O for Other (Could be inherited from PartsServices table)
    fldChargeAmount NUMBER(18, 2) ,                 --  Part or Service Amount (Could be inherited from PartsServices table)
    fldDiscountPercent NUMBER(2),                    --  Part or Service Discount (Could be inherited from PartsServices table)
    fldRemarks VARCHAR2(40),
    fldStatus CHAR(1) NOT NULL,                                 --  A for Active, I for Inactive
    fldOrderId VARCHAR2(10) NOT NULL,
    fldTechnicianId VARCHAR2(10),           -- Null if no technician has been assigned
    CONSTRAINT Charge_pk PRIMARY KEY (fldChargeId),
    CONSTRAINT fk_Order FOREIGN KEY (fldOrderId) REFERENCES tblJobOrders(fldOrderId),
    CONSTRAINT fk_Technician FOREIGN KEY (fldTechnicianId) REFERENCES tblTechnicians(fldTechnicianId),
    CONSTRAINT fk_ChargeReference FOREIGN KEY (fldChargeReference) REFERENCES tblPartsServices(fldPartServiceId),
    CONSTRAINT fk_ChargeCode FOREIGN KEY (fldChargeCode) REFERENCES tblPartsServices(fldPartServiceId),
    CONSTRAINT fk_ChargeType FOREIGN KEY (fldChargeType) REFERENCES tblPartsServices(fldPartServiceType),
    CONSTRAINT fk_DiscountPercent FOREIGN KEY (fldDiscountPercent) REFERENCES tblPartsServices(fldDiscountPercent)
);

Upvotes: 1

Views: 1611

Answers (1)

The Fabio
The Fabio

Reputation: 6250

As you observed a foreign key is a link to the primary key of another table. This website can help with the concept.

You might want to consider adding a trigger to table tblOrderCharges that selects the necessary columns from other tables and places the selected values in tblOrderCharges. The filtering criteria you can specify for handling the non-unique values returned. Would be something like this:

create or replace trigger myexampletrigger
before insert or update
on tblOrderCharges
for each row
DECLARE
// your variables here
BEGIN
  select fldDiscountPercent,fldPartServiceType into YourVariable1,YourVariable2 from tblPartsServices where /*your criteria here*/;

  :new.fldDiscountPercent := YourVariable1;
  :new.fldChargeType := YourVariable2;
END;
/

Upvotes: 3

Related Questions