Icon View Incident Report

Minor Minor
Reported By: Randon ASE
Reported On: 8/21/2007
For: Version 1.05 Build 2
# 2449 Error Not Raised when CASCADE Option Used with Foreign Key Definitions

When i try to update one or more column on main table i have a error message with foreign key on child table even i don't update index columns (FA_PIECE,FA_NUM) , foreigns keys are not very important for me but i had never problem before with other databases.

MAIN TABLE:

CREATE TABLE P_FACTURES
(
FA_PIECE VARCHAR(4) NOT NULL ,
FA_Num INTEGER NOT NULL ,
FA_TYPE CHAR(1) DEFAULT 'F',
FA_STOCK CHAR(1) DEFAULT 'O',
FA_EXPORT CHAR(1) DEFAULT 'N',
FA_CDE VARCHAR(20) ,
FA_DATE TIMESTAMP,
FA_HEURE Time,
FA_CODE_CLIENT VARCHAR(10) ,
FA_CLIENT_DIVERS CHAR(1) DEFAULT 'N',
FA_ITEM_LIVR INTEGER DEFAULT 0,
FA_Haut_page CLOB,
FA_Bas_page CLOB,
FA_BUREAU SMALLINT DEFAULT 0,
FA_VENDEUR VARCHAR(6),
FA_REVIENT DOUBLE,
FA_TOTAL_REM1 DOUBLE,
FA_TOTAL_REM2 DOUBLE,
FA_TOTAL_ESC DOUBLE,
FA_TX_ESC DOUBLE,
FA_PRODUIT_HT DOUBLE,
FA_TRANSPORT SMALLINT DEFAULT 0,
FA_PORT DOUBLE,
FA_PCODETVA SMALLINT, 
FA_EMBALLAGE DOUBLE,
FA_ECODETVA SMALLINT, 
FA_TOTAL_HT DOUBLE,
FA_BASE_EXO DOUBLE,
FA_BASE_EXPORT DOUBLE,
FA_BASE_TVA1 DOUBLE,
FA_TVA1 DOUBLE,
FA_TXTVA1 DOUBLE ,
FA_BASE_TVA2 DOUBLE,
FA_TVA2 DOUBLE,
FA_TXTVA2 DOUBLE ,
FA_BASE_TVA3 DOUBLE,
FA_TVA3 DOUBLE,
FA_TXTVA3 DOUBLE ,
FA_BASE_TVA4 DOUBLE,
FA_TVA4 DOUBLE,
FA_TXTVA4 DOUBLE ,
FA_BASE_TVANPR DOUBLE,
FA_TVANPR DOUBLE,
FA_TXTVANPR DOUBLE ,
FA_BASE_TAX1 DOUBLE,
FA_TAUX_TAX1 DOUBLE,
FA_TXBASE_TAX1 DOUBLE,
FA_TAX1 DOUBLE,
FA_CTVA_TAX1 SMALLINT, 
FA_BASE_OMR DOUBLE,
FA_TAUX_OMR DOUBLE,
FA_TXBASE_OMR DOUBLE,
FA_OMR DOUBLE,
FA_BASE_TAX3 DOUBLE,
FA_TAUX_TAX3 DOUBLE,
FA_TXBASE_TAX3 DOUBLE,
FA_TAX3 DOUBLE,
FA_CTVA_TAX3 SMALLINT, 
FA_TOTAL_TTC DOUBLE PRECISION DEFAULT 0,
FA_POIDS DOUBLE,
FA_VOL DOUBLE,
FA_COLIS DOUBLE,
FA_ECHEANCE TIMESTAMP,
FA_MOREGL SMALLINT,
FA_REGL DOUBLE PRECISION DEFAULT 0,
FA_ACOMPTE DOUBLE PRECISION DEFAULT 0,
FA_DEVISE VARCHAR(3) DEFAULT 'EUR',
FA_TAUX DOUBLE,
FA_ANNULER CHAR(1) DEFAULT 'N',
FA_COMPTA CHAR(1) DEFAULT 'N',
FA_IMPRIME CHAR(1) DEFAULT 'N',
FA_VALIDE CHAR(1) DEFAULT 'N',
FA_MAJ CHAR(1) DEFAULT 'N',
FA_UTILISATEUR VARCHAR(6),
FA_SAISIE_LE TIMESTAMP,
FA_EXERCICE smallint ,
FA_Version integer default 0,
CONSTRAINT FA_PIECE_IDX PRIMARY KEY (FA_PIECE,FA_NUM)
).

CHILD TABLE

CREATE TABLE P_FA_LIGNE
(
FAL_PIECE VARCHAR(4) NOT NULL ,
FAL_Num INTEGER NOT NULL ,
FAL_Ligne INTEGER NOT NULL ,
FAL_TYPE CHAR(1) DEFAULT 'F',
FAL_STOCK CHAR(1) DEFAULT 'O',
FAL_DATE TIMESTAMP,
FAL_HEURE Time,
FAL_ITEM_PRODUIT INTEGER DEFAULT 0,
FAL_REF_PRODUIT VARCHAR(20),
FAL_CODE_CLIENT VARCHAR(10),
FAL_VENDEUR VARCHAR(6),
FAL_UTLISATEUR VARCHAR(6),
FA_SAISIE_LE TIMESTAMP,
FAL_LIBELLE VARCHAR(50) ,
FAL_POIDS_ELEMENT DOUBLE,
FAL_Couleur VARCHAR(25) DEFAULT '',
FAL_Type_col VARCHAR(25) DEFAULT '',
FAL_QTE_ELEMENT DOUBLE,
FAL_UNITE_VTE DOUBLE,
FAL_LIB_UNITE_VTE VARCHAR(20) ,
FAL_QTE_PAR_CAISSE DOUBLE,
FAL_VOL_PAR_CAISSE DOUBLE,
FAL_NBRE_CAISSE_FACT DOUBLE,
FAL_NBRE_CAISSE_LIVR DOUBLE,
FAL_POIDS_TOTAL DOUBLE,
FAL_PUHT DOUBLE,
FAL_TXREM1 DOUBLE,
FAL_TXREM2 DOUBLE,
FAL_ESCPTE DOUBLE,
FAL_PRODUIT_HT DOUBLE,
FAL_PORT_UNITAIRE DOUBLE,
FAL_PORT_TOTAL DOUBLE,
FAL_TOTAL_HT DOUBLE,
FAL_MONTANT_TVA DOUBLE,
FAL_MONTANT_TVA_PORT DOUBLE,
FAL_MONTANT_TAXE1 DOUBLE,
FAL_MONTANT_OMR DOUBLE,
FAL_MONTANT_TAXE3 DOUBLE,
FAL_TOTAL_TTC DOUBLE,
FAL_Code_TVA SMALLINT DEFAULT 0,
FAL_Code_TVA_PORT SMALLINT DEFAULT 0,
FAL_EXOR CHAR(1) DEFAULT 'N' ,
FAL_EXPORT CHAR(1) DEFAULT 'N' ,
FAL_TAXE1 CHAR(1) DEFAULT 'N' ,
FAL_OMR CHAR(1) DEFAULT 'N' ,
FAL_TAXE3 CHAR(1) DEFAULT 'N' ,
FAL_FT INTEGER DEFAULT 0,
FAL_LOT VARCHAR(25) DEFAULT '',
FAL_BUREAU SMALLINT DEFAULT 0,
FAL_TYPE_ART SMALLINT DEFAULT 0,
FAL_REVIENT DOUBLE,
FAL_Tarif SMALLINT DEFAULT 0,
FAL_EXERCICE smallint ,
FAL_Version integer default 0,
CONSTRAINT FAL_Pnum_IDX PRIMARY KEY (FAL_PIECE,FAL_Num,FAL_Ligne)
CONSTRAINT fkSFAL_Pnum FOREIGN KEY ( FAL_PIECE,FAL_Num )
REFERENCES P_FACTURES (FA_PIECE,FA_Num )
ON UPDATE CASCADE ON DELETE CASCADE
)



Comments Comments and Workarounds
EDB should be issuing an error with CASCADE since it only supports NONE or RESTRICT for FK constraints. Dropping the constraint and re-adding it with RESTRICT or NONE update/delete actions will fix the issue.


Resolution Resolution
Fixed Problem on 8/23/2007 in version 1.06 build 1


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image