SELECT @MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @ContractID
SET @NewSeqID = @MaxSeqID + 1
--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @ContractID, @NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @ContractID
I am getting the following message when I am trying to insert into my table. Request is the primary keys and the Bus_Req_Id and Test_Case_Id are both foreign keys. The data that is being inserted into these fields are not unique.
This is the exact error message:
Violation of PRIMARY KEY constraint 'PK_REQUEST_BUS_REQ_TST'. Cannot insert duplicate key in object 'REQUEST_BUS_REQ_TST'. The statement has been terminated.
I am trying to insert 700 - 900 rows based on certain criteria. If I delete the keys the inserts will work.
Any help would be most greatful. Thank you in advance
I know what this error means is that you can not insert duplicate primary keys in the table but the thing is I am checking the rows if they do not exist then insert otherwise don't do any thing these are the lines I am writing in my strored procedure can someone please let me know what I am doing wrong here.
If not exists (Select * From GGP WHERE FFECTIVE_DATE =@v_EFFECTIVE_DATE AND ASSET_ID= @v_ASSET_ID AND ASSET_TYPE = @v_ASSET_TYPE AND Value = @v_Value AND hour = @v_Hour)
INSERT INTO GGP (ASSET_ID,ASSET_TYPE,Hour,Value,EFFECTIVE_DATE) values(@v_ASSET_ID,@v_ASSET_TYPE,@v_Hour,@v_Value, @v_EFFECTIVE_DATE)
The exact error is Violation of PRIMARY KEY constraint 'PK_SP_GGP'. Cannot insert duplicate key in object
When I try to insert data into a table (let's just call it MyTable for this post), I suddenly get the following error.
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'MyTable'.
My table does have a primary key field named 'id', which is an auto-incrementing BIGINT. The last record I successfully inserted received 14 in the id field, so I'm assuming the database is trying to assign 15 to the next. Unfortunately, there is already a record with an id of 15; the next available id is 21.
Is there a way to avoid these primary key collisions?
I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:
alter table databasesize add constraint pk_dbsize primary key (dbid)
But I get the error message:
Table 'databasesize' already has a primary key defined on it.
I'm trying to insert records into a table and getting the below error and not sure how to resolve it:
Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_propertyItem'. Cannot insert duplicate key in object 'propertyItem'. The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_tbl_others. Cannot insert duplicate key in object 'tbl_others. The statement has been terminated.-- What does it mean i cant load the data to tbl_others
Hi allWould there be a easy way to find the column name(s) which constitutea Primary constraint for a table through navigating the systemcatalogs.I found that the PK Constraint object in syscontraints is showing thecolid = 0.TIANorman
When replication merge, is taking place between two servers on one particular table I keep getting violation of primary key constraint, Does anyone out there have a fix for this?
I've a set of source tables on one server and a set of destination tables on another. When I move data from one server to next, I get an error saying "Violation of Primary Key Constraint". This happens for only one table among the 6 tables that I have. I dont understand why it throws me this error. Though theres an error for this table, the data from source table moves into this destination table.
I've a set of source tables on one server and a set of destination tables on another. I'm moving this using a data flow task. When I move data from one server to next, I get an error saying "Violation of Primary Key Constraint". This happens for only one table among the 6 tables that I have. I dont understand why it throws me this error. Though theres an error for this table, the data from source table moves into this destination table.
I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?
Executing the query "BACKUP DATABASE [msdb] TO DISK = N'd:Sql backups..." failed with the following error: "Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB7WERB85D3'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (16771).Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi there,I have created a hash table. After using it, somehow the primary keyconstraint of this hash table still exist in database. Which causeerror.When I delete this constraint with Alter table Drop con....It gives no table exist error.Can anybody give any idea.Thanks in Adv.,T.S.Negi
I am running merg replication (pull) between 7 various sites and once in a while when two people update information in a table in close proximty in time, I will get a primary key violation when viewing conflicts, it's almost excluslively in one table. What is the best way to handle this error. Should I delete the entire column on the subscriber and let the publisher update the subscriber? Or should I redesing the table and primary keys? Any assistance in the matter would be greatly appreciated.
Hey there :)I'm using Ruby on Rails to create a migration of a legacy database onan MS SQL Server. I'd like to name my constraints myself, such as'pk_authors', but in the cases where a table has an auto incrementedIDENTITY(1,1) id field, Rails takes over and adds the primary keyitself when creating the table.This is fine, except then the constraint gets a name like'PK__authors_384934' which is not very intuitive or easily remembered;)I'd like to just throw in an 'ALTER TABLE authors...' statement justafter that table has been created, but I'm not sure how to go aboutrenaming the auto generated constraint since the name it gets ispartially random. Is there any way to indirectly refer to theconstraint like 'RENAME PRIMARY KEY CONSTRAINT ON authors TOpk_authors' or something like that, so that I can rename theconstraint?Thanks in advance,Daniel Buus :)
Hi All, I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key! now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row? many thanks
I have a copy of this database table and the first thing I noticed was that the Primary Key was pretty much useless and there were no sensible indexes. Every query hitting this table ended up table scanning.So I thought I would try dropping the existing Primary Key constraint and then creating a more natural key that would make data retrieval quicker (hopefully). I understand that creating a clustered index on this table is going to take a long time as ALL the data will need to be reorganised (I estimate this will take at least 1 hour). However, just dropping the existing Primary Key constraint is taking forever.I can see that the server is doing a lot of disk reading/ writing and the wait type in Activity Monitor is PAGEIOLATCH_EX.I would have thought that just dropping a primary key would not change the data in the table, just delete the associated index.
In my SSIS package i am loading data from 1 source database to 2 targets database, both targets have same structure and datatype.
My package is working fine for 1 target database but for 2 nd database its giving error "Violation of PRIMARY KEY constraint" but where as primary key constraint is not violating .
Cannot find an answer to this in previous posting, though there are similar topics.
My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:
Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:
Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.
Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.
the point here that i have a small table with two fileds, ID (guid) as primerykey RAF(char) and the table is empty when i add a new row i recieve this exception, Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'. i found no way to solve the problem. thanks in advans
We scheduled lot of SSIS packages on 64 bit server and most of the time they are running fine. Some times we are getting below error message on any random package. When we re-run its working fine to success. This is repeating once in a week on different packages.
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_TblObject'. Cannot insert duplicate key in object 'dbo.tblObject'."
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes) 2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes) 3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState) 4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it. 5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change DECLARE@size varchar(5)--The new size of the column DECLARE @TSQL varchar(255)--Contains the code to be executed DECLARE @Object varchar(50)--Holds the name of the table DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10) ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
I have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.
I am trying to create table with following SQL script:
Code Snippet
create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) constraint NN_Prj_Name not null, Creator nvarchar (255), CreateDate datetime );
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact) ... < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) not null, Creator nvarchar (255), CreateDate datetime ); This script executes correctly, however I want named constraints and this does not satisfy me.
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID.
The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
