Key violation in append query

[Stapes]

Hi

I am trying to run the following:

INSERT INTO TM_Courses ( COURSE_NO, COURSE, FK_PROVIDER )
SELECT [Price List _ pjk_aug07 Without Matching TM_Courses].[Course
Code], [Price List _ pjk_aug07 Without Matching TM_Courses].[Course
Name], 1 AS Expr1
FROM [Price List _ pjk_aug07 Without Matching TM_Courses];

The table TM_Courses has as its first field an autonumber primary key
field (not referenced in my query) called PK_COURSE.

It keeps throwing up a key violation message and not performing the
update. Why is this happening - I am not touching the blooming key
field? And how do I get it to work?

Stapes

 

[Stefan Hoffmann]

hi,

INSERT INTO TM_Courses ( COURSE_NO, COURSE, FK_PROVIDER )

One of these three fields has a unique key or is a primary key.

It keeps throwing up a key violation message and not performing the
update. Why is this happening - I am not touching the blooming key
field? And how do I get it to work?

You need to identify the unique key fields, if guess, I would say it's
COURSE_NO. So a possible solution:

INSERT INTO TM_Courses ( COURSE_NO, COURSE, FK_PROVIDER )
SELECT p.[Course Code], p.[Course Name], 1 AS Expr1
FROM [Price List _ pjk_aug07 Without Matching TM_Courses] C
WHERE NOT EXISTS(
SELECT *
FROM TM_Courses i
WHERE i.COURSE_NO = p.[Course Code]
);



mfG
--> stefan <--

 

[Stapes]

hi,

INSERT INTO TM_Courses ( COURSE_NO, COURSE, FK_PROVIDER )

One of these three fields has a unique key or is a primary key.

It keeps throwing up a key violation message and not performing the
update. Why is this happening - I am not touching the blooming key
field? And how do I get it to work?

You need to identify the unique key fields, if guess, I would say it's
COURSE_NO. So a possible solution:

INSERT INTO TM_Courses ( COURSE_NO, COURSE, FK_PROVIDER )
SELECT p.[Course Code], p.[Course Name], 1 AS Expr1
FROM [Price List _ pjk_aug07 Without Matching TM_Courses] C
WHERE NOT EXISTS(
SELECT *
FROM TM_Courses i
WHERE i.COURSE_NO = p.[Course Code]
);

mfG
--> stefan <--

No - COURSE_NO is not a unique key field - that is just it - I DON'T
reference a unique key field!!

Stapes

 

[Stefan Hoffmann]

hi Stapes,

No - COURSE_NO is not a unique key field - that is just it - I DON'T
reference a unique key field!!

Then there must be field in your table TM_Courses which is unique and
filled with a default value. Take a look at the table definition.


mfG
--> stefan <--

 

[Stapes]

hi Stapes,


Then there must be field in your table TM_Courses which is unique and
filled with a default value. Take a look at the table definition.

mfG
--> stefan <--

No - I already told you - there is not.
Here is my table definition:-

PK_COURSE - Autonumber - Primary key - Indexed (No Duplicates) - New
Values: Increment
COURSE_NO - Text - Indexed (Duplicates OK)
FK_PROVIDER - Number - Not indexed - Foreign Key to table TM_PROVIDERS

OK.

 

[Stefan Hoffmann]

hi Stapes,

FK_PROVIDER - Number - Not indexed - Foreign Key to table TM_PROVIDERS

Default value 0?


mfG
--> stefan <--

 

[Stapes]

hi Stapes,


Default value 0?

mfG
--> stefan <--

Yes - but even when I removed FK_PROVIDER from the query I still got
the same error.

 

[Baz]

Stefan is suggesting that FK_PROVIDER may have a default value (probably 0)
defined on the table. If so, it doesn't matter whether you include it in
the query or not, Access will still try to insert the default value, hence a
possible foreign key violation.

n.b. I understand that you are frustrated, but posting the same problem
twice within 7 minutes is a pretty good way to annoy people and hence lessen
your chances of getting a reply.