Mixing
Teradata identity column and “Duplicate unique prime key error in dbname.tablename”
I created a table using the below definition for a Teradata identity column:
ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100000000
NO CYCLE),
----
UNIQUE PRIMARY INDEX ( ID )
For several months, the ID column has been working properly, automatically generating a unique value for the column. Over the past month, however, ELMAH has been intermittently reporting the following exception from our .NET 4.0 ASP.NET app:
Teradata.Client.Provider.TdException: [Teradata Database] [2801] Duplicate unique prime key error in DATABASENAME.TABLENAME.
I was able to replicate it by opening SQL Assistant and inserting a bunch of records into the table with raw SQL. As expected, most of the time it would insert successfully, but other times it would throw the above exception.
It appears that this error is occuring because Teradata is trying to generate a value for this column that it has previously generated.
Does anyone have any idea how to get to the bottom of what's happening? At the very least, I'd like some way to debug the issue a bit deeper.
unique identity teradata
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
add a comment |
I created a table using the below definition for a Teradata identity column:
ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100000000
NO CYCLE),
----
UNIQUE PRIMARY INDEX ( ID )
For several months, the ID column has been working properly, automatically generating a unique value for the column. Over the past month, however, ELMAH has been intermittently reporting the following exception from our .NET 4.0 ASP.NET app:
Teradata.Client.Provider.TdException: [Teradata Database] [2801] Duplicate unique prime key error in DATABASENAME.TABLENAME.
I was able to replicate it by opening SQL Assistant and inserting a bunch of records into the table with raw SQL. As expected, most of the time it would insert successfully, but other times it would throw the above exception.
It appears that this error is occuring because Teradata is trying to generate a value for this column that it has previously generated.
Does anyone have any idea how to get to the bottom of what's happening? At the very least, I'd like some way to debug the issue a bit deeper.
unique identity teradata
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
add a comment |
I created a table using the below definition for a Teradata identity column:
ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100000000
NO CYCLE),
----
UNIQUE PRIMARY INDEX ( ID )
For several months, the ID column has been working properly, automatically generating a unique value for the column. Over the past month, however, ELMAH has been intermittently reporting the following exception from our .NET 4.0 ASP.NET app:
Teradata.Client.Provider.TdException: [Teradata Database] [2801] Duplicate unique prime key error in DATABASENAME.TABLENAME.
I was able to replicate it by opening SQL Assistant and inserting a bunch of records into the table with raw SQL. As expected, most of the time it would insert successfully, but other times it would throw the above exception.
It appears that this error is occuring because Teradata is trying to generate a value for this column that it has previously generated.
Does anyone have any idea how to get to the bottom of what's happening? At the very least, I'd like some way to debug the issue a bit deeper.
unique identity teradata
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
I created a table using the below definition for a Teradata identity column:
ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100000000
NO CYCLE),
----
UNIQUE PRIMARY INDEX ( ID )
For several months, the ID column has been working properly, automatically generating a unique value for the column. Over the past month, however, ELMAH has been intermittently reporting the following exception from our .NET 4.0 ASP.NET app:
Teradata.Client.Provider.TdException: [Teradata Database] [2801] Duplicate unique prime key error in DATABASENAME.TABLENAME.
I was able to replicate it by opening SQL Assistant and inserting a bunch of records into the table with raw SQL. As expected, most of the time it would insert successfully, but other times it would throw the above exception.
It appears that this error is occuring because Teradata is trying to generate a value for this column that it has previously generated.
Does anyone have any idea how to get to the bottom of what's happening? At the very least, I'd like some way to debug the issue a bit deeper.
unique identity teradata
unique identity teradata
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
asked Jul 30 '12 at 19:06
oscilatingcretinoscilatingcretin
3,7482194166
3,7482194166
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE
EDIT:
If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY column. Personal preference would be to use a negative domain.
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
Doesn'tgenerated alwaysprevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.
– oscilatingcretin
Jul 30 '12 at 23:00
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
In this instance, I was relying on Teradata to generate the values. This means I would useinsert into mydb.employee (name, email) values ('bob', 'bob@blob.com')as opposed toinsert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?
– oscilatingcretin
Jul 31 '12 at 12:09
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f11727972%2fteradata-identity-column-and-duplicate-unique-prime-key-error-in-dbname-tablena%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE
EDIT:
If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY column. Personal preference would be to use a negative domain.
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
Doesn'tgenerated alwaysprevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.
– oscilatingcretin
Jul 30 '12 at 23:00
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
In this instance, I was relying on Teradata to generate the values. This means I would useinsert into mydb.employee (name, email) values ('bob', 'bob@blob.com')as opposed toinsert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?
– oscilatingcretin
Jul 31 '12 at 12:09
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
|
show 3 more comments
I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE
EDIT:
If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY column. Personal preference would be to use a negative domain.
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
Doesn'tgenerated alwaysprevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.
– oscilatingcretin
Jul 30 '12 at 23:00
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
In this instance, I was relying on Teradata to generate the values. This means I would useinsert into mydb.employee (name, email) values ('bob', 'bob@blob.com')as opposed toinsert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?
– oscilatingcretin
Jul 31 '12 at 12:09
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
|
show 3 more comments
I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE
EDIT:
If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY column. Personal preference would be to use a negative domain.
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE
EDIT:
If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY column. Personal preference would be to use a negative domain.
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
edited Jul 31 '12 at 11:52
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
answered Jul 30 '12 at 20:56
Rob PallerRob Paller
7,0351722
7,0351722
Doesn'tgenerated alwaysprevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.
– oscilatingcretin
Jul 30 '12 at 23:00
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
In this instance, I was relying on Teradata to generate the values. This means I would useinsert into mydb.employee (name, email) values ('bob', 'bob@blob.com')as opposed toinsert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?
– oscilatingcretin
Jul 31 '12 at 12:09
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
|
show 3 more comments
Doesn'tgenerated alwaysprevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.
– oscilatingcretin
Jul 30 '12 at 23:00
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
In this instance, I was relying on Teradata to generate the values. This means I would useinsert into mydb.employee (name, email) values ('bob', 'bob@blob.com')as opposed toinsert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?
– oscilatingcretin
Jul 31 '12 at 12:09
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
Doesn't
generated always prevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.– oscilatingcretin
Jul 30 '12 at 23:00
Doesn't
generated always prevent you from inserting your own value into the identity column? For reasons that I won't explain for the sake of keeping this simple, I require the ability to specify my own value for this field when inserting a new record, thus overriding Teradata's behavior of assigning a value itself. When I want the database to generate it for me, I just don't include the field name in the insert statement.– oscilatingcretin
Jul 30 '12 at 23:00
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
When you encountered this error were you providing values in your raw SQL for the UPI or were you relying on Teradata to generate the identity values for you?
– Rob Paller
Jul 31 '12 at 11:49
In this instance, I was relying on Teradata to generate the values. This means I would use
insert into mydb.employee (name, email) values ('bob', 'bob@blob.com') as opposed to insert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?– oscilatingcretin
Jul 31 '12 at 12:09
In this instance, I was relying on Teradata to generate the values. This means I would use
insert into mydb.employee (name, email) values ('bob', 'bob@blob.com') as opposed to insert into mydb.employee (id, name, email) values (100, 'bob', 'bob@blob.com'). This has all been working since probably around February of this year. Now we're starting to get this in our dev environment. I am wanting to fix it before it starts happening in production because that would be bad. When you say negative domain, do you mean use negative numbers for the primary key? So someone's ID could be -100?– oscilatingcretin
Jul 31 '12 at 12:09
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
The ID column being populated by the IDENTITY column is a surrogate key that is being used as the primary index. A negative domain would ensure that values provided by a human never interfere with the domain you have allocated for Teradata to use in generating the IDENTITY value. The primary key is a logical construct that enforces uniqueness of an Employee. The primary index (unique or not) in Teradata is a physical construct that determines data distribution across the AMPS.
– Rob Paller
Jul 31 '12 at 13:26
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
Can you create a copy of this table with the IDENTITY column defined as GENERATED ALWAYS ... NO CYCLE and try to reproduce the problem in development?
– Rob Paller
Jul 31 '12 at 13:28
|
show 3 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f11727972%2fteradata-identity-column-and-duplicate-unique-prime-key-error-in-dbname-tablena%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
