At 02:35 PM 9/07/2003 +0200, you wrote:

>I'm working with charset ISO8859_1
>my table is :
>CREATE TABLE test
>(RSDGI VARCHAR(67) NOT NULL COLLATE FR_FR,
> CODE_DEPT CHAR(3) NOT NULL,
> CODE_COMMUNE CHAR(3) NOT NULL,
> CODE_VOIE CHAR(4) NOT NULL COLLATE FR_FR,
> RS VARCHAR(67) NOT NULL COLLATE FR_FR
>);
>
>Of course a unique key or a primary key on
>(RSDGI,CODE_DEPT,CODE_COMMUNE,CODE_VOIE) is too long
>but I have to be sure that a record based on
>(RSDGI,CODE_DEPT,CODE_COMMUNE,CODE_VOIE) is unique, how can I do ?

I would do a BCN normalisation of those collated columns, putting them into
tables of their own with surrogate primary keys and a unique constraint on
the values themselves.
e.g.
create table RSDGI (
RSDGI_Key integer not null Primary Key,
RSDGI varchar(67) not null COLLATE FR_FR,
constraint UQ_RSDGI Unique(RSDGI));

create table CODE_VOIE (
CODE_VOIE_Key integer not null,
CODE_VOIE varchar(4) not null COLLATE FR_FR,
constraint PK_CODE_VOIE Primary Key(CODE_VOIE_Key)
constraint UQ_CODE_VOIE Unique(CODE_VOIE));

Then, when they are used in tables, you put the primary key into these
tables, not the value itself, place a unique constraint on these key
elements, and use a surrogate key for that table as well.

CREATE TABLE test
(test_pk integer not null primary key,
RSDGI integer NOT NULL ,
CODE_DEPT CHAR(3) NOT NULL, /* no collating??? */
CODE_COMMUNE CHAR(3) NOT NULL, /* no collating??? */
CODE_VOIE integer NOT NULL,
RS varchar(67) NOT NULL COLLATE FR_FR,
constraint UQ_test_keys UNIQUE (RSDGI, CODE_DEPT, CODE_COMMUNE, CODE_VOIE)
);

This way you avoid the horrors of having to propagate massive, ugly
composite structures into foreign keys and maintain collation sequences in
constraint indexes.

heLen