Missing Identity Values In Primary Keys
May 29, 2012
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
View 4 Replies
ADVERTISEMENT
Jun 24, 2004
HI Folks ,
I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help
Burner
View 3 Replies
View Related
Nov 17, 2003
I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?
thanks for your time,
Andre
View 1 Replies
View Related
Jan 26, 2006
This ain't working
SELECT T.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),
'TableHasIdentity') = 0
AND T.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(C.COLUMN_NAME),'IsPrimary Key') = 1
ORDER BY T.TABLE_NAME,C.COLUMN_NAME
This is giving me bogus results...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID(COLUMN_NAME),'IsPrimaryKe y') = 1
I have PK's all over the place. What gives? Too many cocktails with lunch?
View 1 Replies
View Related
Nov 2, 2006
Hi all!
I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:
== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)
== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)
== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)
The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?
Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?
Any ideas are appreciated.
Thank you,
Ben
View 1 Replies
View Related
Nov 23, 2006
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
View 3 Replies
View Related
Jul 20, 2005
Hello group:I've done alot of reading on this subject somewhat and have found thatmany people have many different opinions on this subject. My questioncenters mainly around using a lookup table to enable users to select apre-defined list of values.I have developed a practice myself of avoiding AutoNumber type datafields for primary keys where the primary key will be related to achild table. Nevertheless, what do most users do with lookup tables?My thoughts are to create a small key value for each value in thelookup table. For example:I might have a Carriers table which shows a list of carriers that Imight ship an order by. One of the entries may be 'Air Freight -Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few exampleswhere the primary key field for each entry like these would beautonumber, or at least, a numeric value. What I like to do is createmy own key, like for 'Air Freight - Overnight', I might use 'AFO' forthe key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Anythoughts on this? Mine are that even tho the users may never see thisvalue - I, as the developer will see it and I tend to prefer a keyvalue based on real data that means something other than anauto-incremented number. In referencing the well-known Northwind.mdbdatabase, I noticed their Categories table used a number field value,like 1, 2, 3....etc, but their customers table used values like'ALFKI' to represent their key values.What are some other thoughts out there? I'm working with Accesscurrently, but this project is about to move to SQL Server.James
View 3 Replies
View Related
Aug 10, 2007
what other method can you use to generate primary keys automatically. My boss disagrees with me using identity for the primary keys. He says it is not professional. I am working on SQL Server 2005 platform. Can anybody advice me?
View 3 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
Jul 23, 2005
How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you
View 1 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
May 16, 2008
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
View 2 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Sep 27, 2005
Hi,
We are getting index problems on 2 of our tables. The corruptions are intermittent and only started occurring on 22 Aug. We have been told by our customer that no changes have occurred on their systems recently, and I know that I haven't put anything new into the application either.
Our customer is running (@@Version)
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Some Questions please.
1. Is there anyway that the application could be causing the corruption.
I have seen this from Paul Randal
"Not possible ever for an app bug to cause database corruption - no matter how hard it tries.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine"
Please forgive any unintended insult, but does Paul actually work for Microsoft on this product? I have seen some of his replies and he seems highly, highly conversant with the product and given my limited knowledge of SQL and DB in general I agree that the application should not be able to cause this problem. I need to be able to demonstrate this to both my boss and our customer and a statement like the above from Paul would go a long way to providing the proof I need.
2. Why would the problem suddenly start on 22 Aug and happen regularly thereafter, given that there is supposedly no change to SQL or the application.
We have not been able to replicate the problem in the test region, even though we are able to process the production files there. The customer is reluctant to start full SQL logging given the performance hit.
We have a program which accepts a file of SQL statements and processes them. The input file can be up to about 50Mb and contain many thousands of statements. The structure of the file is this and the program just parses each statement between the <PGMBCL> and sends it to SQL
<PGMBCL>
DELETE FROM HRQUAARI.ZY8C WHERE NUDOSS = +714307848
<PGMBCL>
INSERT INTO HRQUAARI.ZY8C (NUDOSS,NULIGN,SOCDOS,FILLE1,PERTRT,FILLE2,NUM
TRT,NUMBUL,NUMCUM,CUMANN,CUMPER,DATCUM,MONCUM,IDCY00) VALUES ( +714307848, +0000
01, 'OCM', '20', '0506', ' ', '0', '01', '178', '01', '10', '1753-01-01', +00000
008072.2200, ' ')
etc.. another 36 records ..etc
<PGMBCL>
DELETE FROM HRQUAARI.ZYTD12 WHERE NUDOSS = +714307848 AND CDINFO = '8C'
<PGMBCL>
INSERT INTO HRQUAARI.ZYTD12 (NUDOSS,CDINFO,TIMJIF,TEVERO,NOMBRE,NULMAX,NUC
MAX) VALUES ( +714307848, '8C', '2005-09-23-14.36.05', '0', +00037, +000037, +00
0037)
and the DBCC CHECKTABLE ('HRUKPARI.ZY8c') results are :-
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X1ZY8C' (ID 112771509) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1462733:47) with values (NUDOSS = 712430859 and NULIGN = 48) points to the data row identified by (RID = (1:1453030:105)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X1ZY8C' (ID 112771509) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1468453:32) with values (NUDOSS = 712430858 and NULIGN = 50) points to the data row identified by (RID = (1:1453030:104)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X2ZY8C' (ID 112771509) (index ID 3). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1064449:83) with values (NUDOSS = 712430858 and FILLE1 = '20' and PERTRT = '0507' and FILLE2 = ' ' and NUMTRT = '0' and NUMBUL = '01' and NUMCUM = '958' and CUMANN = '02' and CUMPER = '01' and DATCUM = Jan 1 1753 12:00AM) points to the data row identified by (RID = (1:1453030:104)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X2ZY8C' (ID 112771509) (index ID 3). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1463770:32) with values (NUDOSS = 712430859 and FILLE1 = '20' and PERTRT = '0507' and FILLE2 = ' ' and NUMTRT = '0' and NUMBUL = '01' and NUMCUM = '958' and CUMANN = '04' and CUMPER = '01' and DATCUM = Jan 1 1753 12:00AM) points to the data row identified by (RID = (1:1453030:105)).
DBCC results for 'HRUKPARI.ZY8C'.
There are 352118 rows in 2656 pages for object 'HRUKPARI.ZY8C'.
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'HRUKPARI.ZY8C' (object ID 112771509).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (HRUKPARI.HRUKPARI.ZY8C ).
View 3 Replies
View Related
Nov 15, 2006
"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!
what do u think?
hmmm pls help!
View 1 Replies
View Related
May 16, 2002
Using SQL Svr 7.0. It appears that primary keys are created as nonclustered
unique indexes. Is there a configuration setting I can use to make them be
created as clustered unique indexes?
View 1 Replies
View Related
Apr 4, 2001
If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.
Thanks!
View 4 Replies
View Related
Mar 14, 2000
I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.
Thanks
View 1 Replies
View Related
Jun 15, 2004
Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.
Thanks,
Fady
View 3 Replies
View Related
Feb 19, 2004
HI ,
I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:
'table_name' table
- Unable to create index 'PK_tablename'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
View 4 Replies
View Related
May 17, 2004
In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated.
i.e.
key1 key2
200410 12345
200410 12346
200410 12588
etc for all 200410 there can not be a repeat of any value of key 2
is there a way to have this dual key in MSSQL :confused:
View 1 Replies
View Related
Feb 13, 2006
Hi
How can 2 columns be clubbed together to form a primary key , i mean I have 2 columns job & Batch , need to club them together to form the primary key
How is it done I mean in the design , how to define that both the columns togethter form a primary key ,
Cause when I go to the table , it allows me to create a PK through the EM only for one column
Please help
View 3 Replies
View Related
Oct 17, 2007
Hi there,
is there any way I can use INSERT so if I try to copy duplicate primary key data, it automatically skips the task and moves on to the next data?
View 11 Replies
View Related
May 15, 2008
I have declared a table with a single column to be a Primary key. If I write 2 records, the first comprsing "A" and the second "a" then I get an exception about violating the uniqueness of the key. Is there a way that I can define the key to be case sensitive.
Many thanks
View 1 Replies
View Related
May 4, 2007
I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.
View 5 Replies
View Related
Dec 29, 2007
I am not entirly sure what a forigen key is, is it a unique ID which is the same as the primary key? If adding a foreign key to a table that already has data, will it update each row with a unique ID or will it only create a unique ID on newly inserted records (for the foreign key)? If the foreign key is the same as the primary key then why do we need to even add a foreign key at all?For example a table like: Table Name : Customers--CustomerID uniqueidentity (primary Key)--FirstName nchar Table Name : Orders --OrderID int--CustomerID int Obviuously CustomerID are going to be the same in both tables, so why would you need to add a foreign key on 'CustomerID' to the 'Order' table, can't SQL match the customerID in each table any way? Cheers
View 3 Replies
View Related
Mar 20, 2008
HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it?
View 6 Replies
View Related
May 15, 2001
Hi,
I have a question on pk?
I have a db which has tables which do not have pk?I want to create Pk on each one of them - infact,I want to alter the table , add one more field and make it the pk .
My question is - do I will have to go to each table,alter it and then create the pk on it or do someone have any script or better way to do it?
Any help is appreciated.
View 1 Replies
View Related
May 17, 2001
Hi,
Just a continuation to my earlier queries on replication.
I have a db which I want to replicate - it do not have pk?
I do not want to create pk on existing columns - so I thought of creating one more column in all the tables and make them pk .
Any one has any idea if this will work fine or may give any problem which I should be prepare for.
Any thought appreciated -
pvd
View 1 Replies
View Related
May 6, 2005
I have a 3rd party app which had a primary key with about 5 fields. The last field of this was a trantype. This app had a posting process which uses this in it's sql. Ran rather slow. We added an individual index to this field and cut processing down 90%. It almost seemded like sl server was ignoring this index. Is this because it was the last field in the primary key index?
View 1 Replies
View Related
Sep 10, 1998
Hi All,
I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`.
Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`.
I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file.
TIA and you have a great day.
David Nguyen.
View 3 Replies
View Related
Jun 25, 2002
Newbie question...
I have two tables (categories & listings) which create a many-to-many relationship.
I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).
How does the interim table get populated with the id's?
When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.
Thanks in advance for the assistance.
View 1 Replies
View Related
