Can't append due to "key violation"

[Wyoming]

Hello,

I have a table named CLIENTE_PRODUCTO with the following fields:
ID_CLIENTE_PRODUCTO (PK): Autonumeric
COD_CLIENTE: Text
CODIGO_RAMO: Text
FECHA_VENCIMIENTO: Date
PRIMA: Numeric

And I am trying to run this append query:

Code: Select all

INSERT INTO CLIENTE_PRODUCTO ( COD_CLIENTE )
SELECT "222" AS Expr1;

But it doesn't allow me claiming there is a "Key Violation"
As you have seen in the table description, COD_CLIENTE is not the table PK, however it is the PK in a different table but that table is not mentioned in the query.

I have tried with

Code: Select all

SELECT '222' AS Expr1

And

Code: Select all

SELECT 222 AS Expr1

but neither of them work.

What am I missing?

Thanks!

[HansV]

Does this work?

INSERT INTO CLIENTE_PRODUCTO ( COD_CLIENTE )
VALUES ( "222" )

[Wyoming]

I'm afraid I'm getting the same error message.
The thing is that I managed the successfully append data into this table previously but I must have broken something since then and I can't figure out what it is...

[HansV]

Do you have a unique key on the COD_CLIENTE field, even though it is not the primary key? You can check this in the Indexes dialog when you open the table in design view.

[Wyoming]

No, this field appears as "Indexed : No"

Just to provide more context, this CLIENTE_PRODUCTO table is linked with referential integrity to the table in which COD_CLIENTE is PK in a 1 to N relationship. The CLIENTE_PRODUCTO table is the "N" side of the relationship

[HansV]

Does the other table contain a record with PK = "222"?

[Wyoming]

Nope
In fact, this is just a dummy record for testing purposes, in the other table (it is named CLIENTES) all records have a 12 character code in COD_CLIENTE, so none of them is "222".
In CLIENTE_PRODUCTO there are only 2 records, and the 2nd one was created through an append query that worked successfully. I don't know what I might have changed since then :S

[HansV]

If you have a relationship with referential integrity enforced, you can only create a record in CLIENTE_PRODUCTO with COD_CLIENTE = "222" if there is already a record in the other table with primary key "222". Otherwise, your record would be an orphan - that's the idea behind enforcing referential integrity.

So either use a value that already exists in the COD_CLIENTE field in the other table, or add a record with PK = "222" first, and only then add a record to CLIENTE_PRODUCTO.

[Wyoming]

You got it Hans,

The value did exist in the other table... but it was a different field type, that's why it wasn't recognising it... embarrassing

Thanks a lot!!