key violations when there are no key violations

#1

hello;
Access 2000. I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql server
table. The data I am appending is not a duplicate of what already
exists in the sql server table (the data is not the same as any of the
records).

Does anybody know what could be causing this?

Thanks in advance.

Tony

#2

Tony_E wrote:

Quote:

hello;
Access 2000. I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql server
table. The data I am appending is not a duplicate of what already
exists in the sql server table (the data is not the same as any of the
records).

Does anybody know what could be causing this?

Thanks in advance.

Point of order: a table can only have one primary key. That key might

consist of several columns, but it is still a single key. What can be
confusing is that your table can have one or more unique constraints or
indexes in addition to the primary key. For example, one might have a
primary key on an IDENTITY (autonumber) column in addition to a unique index
on the columns that comprise the table's "natural" key (there are
performance advantages to having a unique clustered index on a single
monotonically increasing column such as an IDENTITY column)

"Primary" is not the only type of key that can exist: there might also be
foreign keys (relationships) that are being violated by your insert.

#3

On Mar 28, 12:18*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

Tony_E wrote:
hello;
Access 2000. *I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql server
table. The data I am appending is not a duplicate of what already
exists in the sql server table (the data is not the same as any of the
records).

Does anybody know what could be causing this?

Thanks in advance.

Point of order: a table can only have one primary key. That key might
consist of several columns, but it is still a single key. What can be
confusing is that your table can have one or more unique constraints or
indexes in addition to the primary key. For example, one might have a
primary key on an IDENTITY (autonumber) column in addition to a unique index
on the columns that comprise the table's "natural" key (there are
performance advantages to having a unique clustered index on a single
monotonically increasing column such as an IDENTITY column)

"Primary" is not the only type of key that can exist: there might also be
foreign keys (relationships) that are being violated by your insert.

Thanks for the response. You were absolutely right, when I tried to
manually add a record to the sql server table, I received a message
ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
KEY constraint etc etc...dbname table name 'orders', column 'ID.'

What do I do now? What do I have to do to make the append work? I
assume the linked field in the relationship of the record (I am
appending) has to already have that value in a record in the sql
server table, I tried it in test and it did not work--same message.
Any ideas?

Thanks
Tony

#4

Tony_E wrote:

Quote:

On Mar 28, 12:18 pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Tony_E wrote:
hello;
Access 2000. I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql
server table. The data I am appending is not a duplicate of what
already exists in the sql server table (the data is not the same as
any of the records).

Does anybody know what could be causing this?

Thanks in advance.

Point of order: a table can only have one primary key. That key might
consist of several columns, but it is still a single key. What can be
confusing is that your table can have one or more unique constraints
or indexes in addition to the primary key. For example, one might
have a primary key on an IDENTITY (autonumber) column in addition to
a unique index on the columns that comprise the table's "natural"
key (there are performance advantages to having a unique clustered
index on a single monotonically increasing column such as an
IDENTITY column)

"Primary" is not the only type of key that can exist: there might
also be foreign keys (relationships) that are being violated by your
insert.

Thanks for the response. You were absolutely right, when I tried to
manually add a record to the sql server table, I received a message
ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
KEY constraint etc etc...dbname table name 'orders', column 'ID.'

What do I do now? What do I have to do to make the append work?
It sounds as if you need to add a record to the related table with the ID of

the record you are adding to the first table ...it's hard to discuss these
tables in the abstract. In other words, there is a relationship (foreign
key) between a "master" table and a "child" table, so a "master" record has
to be in the "master" table before the "child" record can be added into the
"child" table.

Quote:

I assume the linked field in the relationship of the record (I am
appending) has to already have that value in a record in the sql
server table, I tried it in test and it did not work--same message.

You tried to insert a record into the master table and received exactly the
same message? That means the master table has its own master, I guess. Is
there a dba for that sql server who you can ask for advice? Or the designer
of the database at least? Again, it's hard to discuss this in the abstract.
I would need to see at least the ddl (data definition language -
http://www.karaszi.com/SQLServer/inf...ate_script.asp) scripts for the
tables involved to get more specific.

#5

On Mar 28, 4:48*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

Tony_E wrote:
On Mar 28, 12:18 pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Tony_E wrote:
hello;
Access 2000. I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql
server table. The data I am appending is not a duplicate of what
already exists in the sql server table (the data is not the same as
any of the records).

Does anybody know what could be causing this?

Thanks in advance.

Point of order: a table can only have one primary key. That key might
consist of several columns, but it is still a single key. What can be
confusing is that your table can have one or more unique constraints
or indexes in addition to the primary key. For example, one might
have a primary key on an IDENTITY (autonumber) column in addition to
a unique index on the columns that comprise the table's "natural"
key (there are performance advantages to having a unique clustered
index on a single monotonically increasing column such as an
IDENTITY column)

"Primary" is not the only type of key that can exist: there might
also be foreign keys (relationships) that are being violated by your
insert.

Thanks for the response. You were absolutely right, when I tried to
manually add a record to the sql server table, I received a message
ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
KEY *constraint etc etc...dbname table name 'orders', column 'ID.'

What do I do now? What do I have to do to make the append work?

It sounds as if you need to add a record to the related table with the IDof
the record you are adding to the first table ...it's hard to discuss these
tables in the abstract. In other words, there is a relationship (foreign
key) between a "master" table and a "child" table, so a "master" record has
to be in the "master" table before the "child" record can be added into the
"child" table.

I assume the linked field in the relationship of the record (I am
appending) has to already have that value in a record in the sql
server table, I tried it in test and it did not work--same message.

You tried to insert a record into the master table and received exactly the
same message? That means the master table has its own master, I guess. Is
there a dba for that sql server who you can ask for advice? Or *the designer
of the database at least? Again, it's hard to discuss this in the abstract.
I would need to see at least the ddl (data definition language -http://www.karaszi.com/SQLServer/info_generate_script.asp) scripts for the
tables involved to get more specific.- Hide quoted text -

- Show quoted text -

Hello;
I made a mistake in my previous explanation--what I meant was that I
checked the master table, examined a certain record, and attempted to
add record to the child table-using the value that already exists in
the master table. I got the same message.

#6

On Mar 29, 8:21*am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Quote:

On Mar 28, 4:48*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Tony_E wrote:
On Mar 28, 12:18 pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Tony_E wrote:
hello;
Access 2000. I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql
server table. The data I am appending is not a duplicate of what
already exists in the sql server table (the data is not the same as
any of the records).

Does anybody know what could be causing this?

Thanks in advance.

Point of order: a table can only have one primary key. That key might
consist of several columns, but it is still a single key. What can be
confusing is that your table can have one or more unique constraints
or indexes in addition to the primary key. For example, one might
have a primary key on an IDENTITY (autonumber) column in addition to
a unique index on the columns that comprise the table's "natural"
key (there are performance advantages to having a unique clustered
index on a single monotonically increasing column such as an
IDENTITY column)

"Primary" is not the only type of key that can exist: there might
also be foreign keys (relationships) that are being violated by your
insert.

Thanks for the response. You were absolutely right, when I tried to
manually add a record to the sql server table, I received a message
ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
KEY *constraint etc etc...dbname table name 'orders', column 'ID.'

What do I do now? What do I have to do to make the append work?

It sounds as if you need to add a record to the related table with the ID of
the record you are adding to the first table ...it's hard to discuss these
tables in the abstract. In other words, there is a relationship (foreign
key) between a "master" table and a "child" table, so a "master" recordhas
to be in the "master" table before the "child" record can be added intothe
"child" table.

I assume the linked field in the relationship of the record (I am
appending) has to already have that value in a record in the sql
server table, I tried it in test and it did not work--same message.

You tried to insert a record into the master table and received exactlythe
same message? That means the master table has its own master, I guess. Is
there a dba for that sql server who you can ask for advice? Or *the designer
of the database at least? Again, it's hard to discuss this in the abstract.
I would need to see at least the ddl (data definition language -http://www.karaszi.com/SQLServer/info_generate_script.asp) scripts for the
tables involved to get more specific.- Hide quoted text -

- Show quoted text -

Hello;
I made a mistake in my previous explanation--what I meant was that I
checked the master table, examined a certain record, and attempted to
add record to the child table-using the value that already exists in
the master table. I got the same message.- Hide quoted text -

- Show quoted text -

Ok, I got passed that primary key foreign key obstacle--these tables
have a one to one relationship -- I have to add a record to the parent
table before adding a record to the child table. So when I attempted
to add a record to the parent table I received a Failure message
referencing a TRIGGER. "Insert in linked table "CUSTOMER_ORDER"
failed. Error in trigger INSERT_CUSTOMER_ORDER. Is there anything I
can do now to insert to this table?

#7

Tony_E wrote:

Quote:

On Mar 29, 8:21 am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Hello;
I made a mistake in my previous explanation--what I meant was that I
checked the master table, examined a certain record, and attempted to
add record to the child table-using the value that already exists in
the master table. I got the same message.- Hide quoted text -

- Show quoted text -

Ok, I got passed that primary key foreign key obstacle--these tables
have a one to one relationship -- I have to add a record to the parent
table before adding a record to the child table. So when I attempted
to add a record to the parent table I received a Failure message
referencing a TRIGGER. "Insert in linked table "CUSTOMER_ORDER"
failed. Error in trigger INSERT_CUSTOMER_ORDER. Is there anything I
can do now to insert to this table?

Ask the database designer or dba? I hesitate to suggest disabling the
trigger without knowing what business rule it is enforcing.

#8

On Mar 29, 8:52*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

Tony_E wrote:
On Mar 29, 8:21 am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Hello;
I made a mistake in my previous explanation--what I meant was that I
checked the master table, examined a certain record, and attempted to
add record to the child table-using the value that already exists in
the master table. I got the same message.- Hide quoted text -

- Show quoted text -

Ok, I got passed that primary key foreign key obstacle--these tables
have a one to one relationship -- I have to add a record to the parent
table before adding a record to the child table. *So when I attempted
to add a record to the parent table I received a Failure message
referencing a TRIGGER. "Insert in linked table "CUSTOMER_ORDER"
failed. *Error in trigger INSERT_CUSTOMER_ORDER. *Is there anythingI
can do now to insert to this table?

Ask the database designer or dba? I hesitate to suggest disabling the
trigger without knowing what business rule it is enforcing.- Hide quoted text -

- Show quoted text -

Yes I think when the append functionality is run, the trigger will
have to be disabled. In one of my other posts you indicated that to
disable I would have to use a passthrough query or VBA and ADO. Could
you give me a sample of how to do it in a passthrough?

#9

Tony_E wrote:

Quote:

On Mar 29, 8:52 am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Tony_E wrote:
On Mar 29, 8:21 am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Hello;
I made a mistake in my previous explanation--what I meant was that
I checked the master table, examined a certain record, and
attempted to add record to the child table-using the value that
already exists in the master table. I got the same message

Ok, I got passed that primary key foreign key obstacle--these tables
have a one to one relationship -- I have to add a record to the
parent table before adding a record to the child table. So when I
attempted to add a record to the parent table I received a Failure
message referencing a TRIGGER. "Insert in linked table
"CUSTOMER_ORDER" failed. Error in trigger INSERT_CUSTOMER_ORDER. Is
there anything I can do now to insert to this table?

Ask the database designer or dba? I hesitate to suggest disabling the
trigger without knowing what business rule it is enforcing

Yes I think when the append functionality is run, the trigger will
have to be disabled. In one of my other posts you indicated that to
disable I would have to use a passthrough query or VBA and ADO. Could
you give me a sample of how to do it in a passthrough?

It's not hard to find via google so OK ... I'm just very hesitant here - do
you have any idea what that trigger is actually doing and what the
consequences of bypassing its functionality will be? Before going down this
road you should at least figure that out - you haven't provided the version
of your sql server so it's hard to get into specifics here. Run this search
to find out how to display the text of the trigger so you can see what it's
for:
http://www.google.com/search?sourcei...r+trigger+text

Then, after you are satisfied that disabling the trigger is safe, run this
search to see how to do it in your version of sql server:
http://www.google.com/search?hl=en&s...1g-b3&aql=&oq=

If you do disable it, you should consider performing its functionality
yourself just to make sure whatever rules it is enforcing are not violated.

#10

On Mar 29, 9:48*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

Tony_E wrote:
On Mar 29, 8:52 am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Tony_E wrote:
On Mar 29, 8:21 am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Hello;
I made a mistake in my previous explanation--what I meant was that
I checked the master table, examined a certain record, and
attempted to add record to the child table-using the value that
already exists in the master table. I got the same message

Ok, I got passed that primary key foreign key obstacle--these tables
have a one to one relationship -- I have to add a record to the
parent table before adding a record to the child table. So when I
attempted to add a record to the parent table I received a Failure
message referencing a TRIGGER. "Insert in linked table
"CUSTOMER_ORDER" failed. Error in trigger INSERT_CUSTOMER_ORDER. Is
there anything I can do now to insert to this table?

Ask the database designer or dba? I hesitate to suggest disabling the
trigger without knowing what business rule it is enforcing

Yes I think when the append functionality is run, the trigger will
have to be disabled. In one of my other posts you indicated that to
disable I would have to use a passthrough query or VBA and ADO. Could
you give me a sample of how to do it in a passthrough?

It's not hard to find via google so OK ... I'm just very hesitant here - do
you have any idea what that trigger is actually doing and what the
consequences of bypassing its functionality will be? Before going down this
road you should at least figure that out - you haven't provided the version
of your sql server so it's hard to get into specifics here. Run this search
to find out how to display the text of the trigger so you can see what it's
for:http://www.google.com/search?sourcei...=SQL+Server+tr...

Then, after you are satisfied that disabling the trigger is safe, run this
search to see how to do it in your version of sql server:http://www.google.com/search?hl=en&s...rver+trigger+d...

If you do disable it, you should consider performing its functionality
yourself just to make sure whatever rules it is enforcing are not violated.- Hide quoted text -

- Show quoted text -

I don't know what version of sql server we use because I don't have
access to SQL Enterprise or Qry Analyzer.
I will definitely have a conversation with my manager before even
testing disable functionality on our test db.
Thanks for the link.