At 12:32 PM 29/11/2008, you wrote:

>Hi, I had a problem with these tables
>
>CREATE TABLE GM_GIACENZA (
> IDREC INTEGER NOT NULL,
> DATA TIMESTAMP NOT NULL,
> ARTICOLO CODICE_ARTICOLO /* CODICE_ARTICOLO = VARCHAR(20)
>NOT NULL */,
> GIACENZA FLOAT NOT NULL,
> LOTTO VARCHAR(6) DEFAULT '',
> DATA_INSERIMENTO TIMESTAMP DEFAULT 'TODAY' NOT NULL
>);
>
>ALTER TABLE GM_GIACENZA ADD CONSTRAINT PK_GM_GIACENZA PRIMARY KEY (IDREC);
>
>CREATE UNIQUE INDEX GM_GIACENZA_IDX1 ON GM_GIACENZA (DATA, ARTICOLO,
>LOTTO);
>
>CREATE TABLE ARTICOAN (
> PROGRESS INTEGER NOT NULL,
> STATO VARCHAR(1) NOT NULL,
> NDITTA DITTA NOT NULL,
> DUMMY01 VARCHAR(2) NOT NULL,
> CODREF VARCHAR(20) NOT NULL,
> DESCR VARCHAR(40) NOT NULL,
> DESCR2 VARCHAR(40) NOT NULL,
> DUMMY02 VARCHAR(10) NOT NULL,
> GRART VARCHAR(4) NOT NULL,
> DUMMY03 VARCHAR(16) NOT NULL,
> GRMERC VARCHAR(2) NOT NULL,
> DUMMY04 VARCHAR(1) NOT NULL,
> FITTIZIO VARCHAR(1) NOT NULL,
> PRFINITO VARCHAR(1) NOT NULL,
> SEMILAV VARCHAR(1) NOT NULL,
> MATPRIMA VARCHAR(1) NOT NULL,
> GESTIONE VARCHAR(1) NOT NULL,
> DUMMY05 VARCHAR(9) NOT NULL,
> UNIMIS VARCHAR(2) NOT NULL,
> DUMMY06 VARCHAR(22) NOT NULL,
> PESOUNIT DECIMAL(9,3) NOT NULL,
> ARAN_MISURA_CONFEZIONE VARCHAR(2) NOT NULL,
> ARAN_PEZZI_CONFEZIONE DECIMAL(9,5) NOT NULL,
> ARAN_CONV_STOCC_CONFEZ DECIMAL(9,4) NOT NULL,
> ARAN_LINEA VARCHAR(3) NOT NULL,
> ARAN_BARCODE VARCHAR(40) NOT NULL,
> ARAN_PRESIDI_SANITARI VARCHAR(1) NOT NULL,
> ARAN_CODICE_PRESIDIO VARCHAR(6) NOT NULL,
> ARAN_MISURA_PRESIDIO_SAN VARCHAR(2) NOT NULL,
> ARAN_CONV_STOCC_PRESIDIO_SAN DECIMAL(9,4) NOT NULL,
> ARAN_REGISTRO_SEMENTI VARCHAR(1) NOT NULL,
> DUMMY_07 VARCHAR(9) NOT NULL,
> NOMENCLA VARCHAR(8) NOT NULL,
> DUMMY08 VARCHAR(32) NOT NULL,
> CLASSE VARCHAR(8) NOT NULL,
> DUMMY09 VARCHAR(312) NOT NULL,
> ARAN_CONTENITORE VARCHAR(1) DEFAULT '' NOT NULL,
> DUMMY10 VARCHAR(11),
> IDCOMMENTO INTEGER NOT NULL,
> UTENTE VARCHAR(10) NOT NULL,
> DATAMOD TIMESTAMP NOT NULL,
> ORAMOD INTEGER NOT NULL
>);
>
>ALTER TABLE ARTICOAN ADD CONSTRAINT PK_ARTICOAN PRIMARY KEY (PROGRESS);
>
>CREATE INDEX ARTICOAN_IDX01 ON ARTICOAN (NDITTA, CODREF);
>CREATE INDEX ARTICOAN_IDX02 ON ARTICOAN (DUMMY01, NDITTA, CODREF);
>CREATE INDEX ARTICOAN_IDX03 ON ARTICOAN (NDITTA, DESCR, CODREF);
>CREATE INDEX ARTICOAN_IDX04 ON ARTICOAN (NDITTA, GRMERC, CODREF);
>CREATE INDEX ARTICOAN_IDX05 ON ARTICOAN (NDITTA, DUMMY02, CODREF);
>CREATE INDEX ARTICOAN_IDX06 ON ARTICOAN (NDITTA, CLASSE, DESCR, CODREF);
>CREATE INDEX ARTICOAN_IDX07 ON ARTICOAN (NDITTA, DUMMY05, CODREF);
>CREATE INDEX ARTICOAN_IDX08 ON ARTICOAN (NDITTA, GESTIONE, CODREF);
>CREATE INDEX ARTICOAN_IDX09 ON ARTICOAN (NDITTA, DUMMY05, DESCR, CODREF);
>CREATE INDEX ARTICOAN_IDX10 ON ARTICOAN (NDITTA, GRART, CODREF);
>CREATE UNIQUE INDEX ARTICOAN_IDX11 ON ARTICOAN (CODREF);
>
>I would like to create a constraint for GM_GIACENZA table on field
>ARTICOLO using ARTICOAN table and field CODREF but when I try to add it..
>
>alter table GM_GIACENZA
>add constraint FK_GM_GIACENZA_1
>foreign key (ARTICOLO)
>references ARTICOAN(CODREF)
>on delete CASCADE
>
>...I had this error:
>
>This operation is not defined for system tables.
>unsuccessful metadata update.
>could not find UNIQUE or PRIMARY KEY constraint in table ARTICOAN with
>specified columns.
>
>As you can see, there's is the ARTICOAN_IDX11 index which contain the
>rquired column and it is set to unique...why it doesn't work ? Can you
>help me to find what's wrong ?

Two things are wrong:

1) in ARTICOAN the referenced column (CODREF) must be unique alone;
2) then, a UNIQUE constraint must be declared for that column ALONE, and committed.

Note, a unique column whose unique index is not the index for a UNIQUE constraint cannot be referenced as the parent for a FOREIGN KEY constraint.

./heLen