Duplicate Unique Prime Key Error In Teradata Mload
Contents |
baseUsers turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for Search instead for Did you mean: Teradata : Product Forums : Database : duplicate unique primary key error Options duplicate unique key error wow Subscribe to RSS Feed Mark Topic as New Mark Topic as Read Float this teradata error 2801 Topic to the Top Bookmark Subscribe Printer Friendly Page sharad Fan Options Mark as New Bookmark Subscribe Subscribe to RSS Feed duplicate unique prime key error informatica Get Direct Link Print Email to a Friend Report Inappropriate Content 01-19-2006 03:46 PM 01-19-2006 03:46 PM duplicate unique primary key error Hi, I had to modify a column's width so i created a temporary duplicate unique prime key error world of warcraft table, with modified column width, and populated that temporary table through (insert into temp select * from original_table).Later I dropped the original_table and renamed the temporary table to the original one. The original_table had id as the unique primary index and was defined as "Id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY". Now I am not able to insert data in this table manually through insert query. It gives me
Teradata Odbc Teradata Driver Teradata Database Duplicate Unique Prime Key Error
error as "duplicate unique primary key error". Any solutions to rectify this situations. Please Help!!Thanks & Regards, 0 Kudos Reply All Forum Topics Previous Topic Next Topic 2 REPLIES Fred Teradata Employee Options Mark as New Bookmark Subscribe Subscribe to RSS Feed Get Direct Link Print Email to a Friend Report Inappropriate Content 01-19-2006 11:00 PM 01-19-2006 11:00 PM Re: duplicate unique primary key error GENERATED BY DEFAULT columns are not guaranteed to be unique anyway, but if you're sure that's what you want: create another table but specify that generated values START WITH a value beyond anything already present in the table; INSERT/SELECT the data, drop old, rename again.Since you have to copy the data to a new table anyway, consider whether GENERATED ALWAYS NOCYCLE would be better for your application. (Of course, then you wouldn't use "SELECT *" because you're not allowed to specify a value to insert into that column). 0 Kudos Reply DEEPU Enthusiast Options Mark as New Bookmark Subscribe Subscribe to RSS Feed Get Direct Link Print Email to a Friend Report Inappropriate Content 01-20-2006 12:29 PM 01-20-2006 12:29 PM Re: duplicate unique primary key error Sharad,UPK error occurs only when the UPI column that you are trying to insert is alraedy present th
baseUsers turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for Search instead for Did you mean: Teradata : Product Forums : Database : Error
Unique Primary Index In Teradata
from Insert Select statement Options Subscribe to RSS Feed Mark Topic as New duplicate unique key 211489 Mark Topic as Read Float this Topic to the Top Bookmark Subscribe Printer Friendly Page donal28 Enthusiast Options Mark as New Bookmark Subscribe Subscribe to RSS Feed Get Direct Link Print Email to a Friend Report Inappropriate Content 06-12-2012 01:58 PM 06-12-2012 01:58 PM Error from Insert Select statement Hi All I am http://community.teradata.com/t5/Database/duplicate-unique-primary-key-error/td-p/383 getting an error when running the following Insert Select statment Statement INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_NAME) select DISTINCT TRIM (CM_CUST_NAME) FROM DDHWV03P.CM_CUSTOMER_STATIC_PERIODIC Error INSERT Failed. 2801:Duplicate unique prime key error in DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO. Neither the BORROWER_NAME or CM_CUST_NAME fields are prime keys and both are the same datatype varchar(255) Would I better using Update Select statement instead? Do I need to join the tables http://community.teradata.com/t5/Database/Error-from-Insert-Select-statement/td-p/57936 to do this Any help on this would be very much appreciated Regards Donal 0 Kudos Reply All Forum Topics Previous Topic Next Topic 8 REPLIES venkylingutla Enthusiast Options Mark as New Bookmark Subscribe Subscribe to RSS Feed Get Direct Link Print Email to a Friend Report Inappropriate Content 06-12-2012 11:42 PM 06-12-2012 11:42 PM Re: Error from Insert Select statement Hi, It means in the table FMP_STRATEGIC_CM_BORROWER_AUTO the colmn contains unique primary index please check. with the sql statement show table
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or http://stackoverflow.com/questions/11727972/teradata-identity-column-and-duplicate-unique-prime-key-error-in-dbname-tablena posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss http://teradata4ed.blogspot.com/2012/04/what-you-need-to-know-before-creating.html Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Teradata identity column and “Duplicate unique prime key error in dbname.tablename” up vote 0 down vote favorite I created a table using the below definition for a Teradata identity column: ID INTEGER GENERATED BY DEFAULT duplicate unique 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 duplicate unique prime 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 share|improve this question asked Jul 30 '12 at 19:06 oscilatingcretin 2,8261468131 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote 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. share|improve this answer edit
covers a handful of the basics of the Teradata architecture, to help new Teradata users better understand what considerations should go into creating a table. Teradata Architecture Teradata uses Massively Parallel Processing (MPP) to provide linear scalability of the system by distributing the data across a number of processing units (AMPs). Each record in a table is placed on an AMP. The more evenly the data is distributed across the AMPs for all tables, the better the system performs, because each AMP does an equal amount of work to satisfy a query. When the data is unevenly distributed, the AMPs with the most records work harder, while the AMPs with the fewest records are underutilized. Key point: Teradata performs the best when data is evenly distributed Primary Index The primary index (PI) distributes the records in a table across the AMPs, by hashing the columns that make up the PI to determine which records go to which AMP. If no PI is specified when a table is created, the first column of the table will be used as the PI. When creating a table, care needs to be taken to choose a column or set of columns that evenly distribute the data across the AMPs. A PI that distributes data unevenly will at the very least impact the performance of the table, and depending on the size of the table, has the potential to negatively impact the entire system. Even distribution of the PI isn't the only criteria to use when choosing a PI. Consideration should also be given to how the data will be queried. If the data can be evenly distributed using different sets of columns, then the determination of which columns to use should be based on how the data will be queried and what other tables it will be joined to. If two tables that are frequently joined have the same PI, then joining them doesn't require the records to be redistributed to other AMPs to satisfy a query. A PI doesn't have to be the same as the primary key (PK) of a table. The purpose of a PI is to evenly distribute the data, while the purpose of a PK is to identify unique records. The PI and PK can be the same, but it isn't required. Key point: Picking the right primary index is critical to ensuring good table and system performance Skew Factor A table that has perfectly distributed data has a skew factor of 0%. The