I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.
What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e
On Server 1 - Values start from 1 then 2,3,4 etc etc
Server 2 - 24001, 24002, 24003 etc etc
Server 3 - 46001, 46002, 46003 etc etc
Server 4 - 68001, 68002, 68003
My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?
Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.
cheers
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I confuse about Identity and primary key, what is the different between them. One table can have no primary key. Right? Thanks.
View 14 Replies View RelatedCan someone tell me the difference between making a column primary key column vs. making it identity column?
thanks
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
What is the different between Primary key and Identity Column? and when should I them?
(Im just a beginner with databases...)
I orginally had my project ID specified as an Int and the properties set as identity specification that would automatically fill in the ID field however I have now changed it as the ID needs to be specified by the user but now when i update a project the ID isnt seen as the identity so whatever i do affects other records not just the one i select.
it is now defined as an nvarchar but i dont know how to set that as the identity so that each record can be edited seperately, can someone please help this is really urgent!!
Im using visual web developer express with sql server, please please help!
I want to crete temporary table with this coammnd "CREATE TABLE Temp (ID int, name varchar(50))". I would like to know which command is used for setting primary key and identity on ID field. Thankyou in advance.
View 2 Replies View RelatedI have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc....:
Sub AddNew_Click(Sender As Object, E As EventArgs)
' add a new row to the end of the data, and set editing mode 'on'
CheckIsEditing("")
If Not isEditing = True Then
' set the flag so we know to do an insert at Update time
AddingNew = True
' add new row to the end of the dataset after binding
' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds)
' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)
' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count
If recordCount > 1 Then
recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize
End If
' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End If
End Sub
Can anyone explain the Difference between Primary Key and Identity Column in MSSQL Server 6.5. Please give me any example if possible.
Thanks for Help!!!!
I am working on a project to consolidate 3 distinct buildings name code(6 digit character called BUILDING_CODE Primary Key) into one building code. This is an effort to merge/move everyone who is currently coded under the three building codes into one building code. We currently have this column defined on 5 tables as primary key (none identity) in SQL Server 2005.
Out of the five tables one has two store procedure dependencies.
What would be the best practice to update the six digit BULDING_CODE column Primary Key value without causing any issues?
We are converting a legacy visual foxpro system to use a SQL back-end.A number of (existing DBF) tables currently have a zero-filled primarykey eg. '000255' which is just an auto-incrementing key - but alwaysstored as a char field with leading zeros.For backward compatibility we are considering retaining this primarykey and using an identity field to auto-generate the next value, thenconvert the new identity value into the new primary key. So ifidentity is 256 then the key field will be assigned '000256'.Now the problem with this is that the primary key must be non-null andunique so must be given a value in the INSERT statement. But, theidentity value isn't available (I presume) until after the INSERTstatement has executed.Is this a "don't go there" kind of problem?ThanksAndrew GrandisonSA Department of HealthAdelaide, South Australia
View 2 Replies View RelatedHello --
Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.
I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.
Any help is appreciated.
Eric
What is diffecence indexes, primary key and identity.
View 1 Replies View RelatedI 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
Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.
HELP!
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?
I have a table to store int primary keys. Where the value nextID is the value to use.I need to get the value and update it (nextID + 1) before the next person gets it.I thought using tran would work but it doesn't stop a select.How do I get a value (lock the table for the update), update the table (preventing any selects until done) and release the table.
BEGIN TRAN
UPDATE TableIds
SET NextId = NextId + 1
WHERE TableName = 'Users'
WAITFOR DELAY '00:00:20'
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
COMMIT TRAN
I really want to do this in the reverse order but it doesn't work. But if I do this and have another query to just do a select:
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
This query never returns. Doesn't the COMMIT TRAN release the lock on the table?
We are developing a batabase which is meant for financial domain,so it will import data from different source system..
and data from our data base will be further passed to other applications.
In contex of our system integration with other data sources ,whether is it a good idea to have a auto integer primary key a or to implement some logic to generate primary key?
Can some one guide us to some pratical data base design case studies?or some best practices.?
Hi,
We will be using SQL Compact Edition in our application. We are doing some tests to make sure that SQL Compact is suitable for our requirments.
Our problem is with Primary keys and how to maintain data integrity since we will have 100+ clients inserting, updating, and synchronizing with SQL Server.
Can we control Identity range?
Shall we use UniqueIdentifires insted of int?
How can we detect errors at the client side? (For example, if two clients are trying to synchronize 2 columns with the same primary key value)
We will use Merge replication.
Thank you
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.
Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.
View 9 Replies View RelatedHow do i find all the tables that have an Identity Column as a primary key in a database. Thanks.
View 8 Replies View RelatedHi 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
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out.
How can I set this field with the IDENTITY properties?
Thanks
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
If I right click on a primary key in SQL Server management studion, I can set the identity specification for a primary keyId so that it is indexible. I do this by setting the properties for "Is Identity", "Identity Increment", and "Identity Seed". This is wonderful, but I have been asked to perform this using a script instead of the SQL server GUI/Sql Server management studio. Is this possible? Does anyone know how to do this? If so, can you show me how?
Ralph
I have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
View 7 Replies View RelatedDoes anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View 2 Replies View RelatedHey guys.
I just wanted to ask you a question? we are having a field declared
as BIT and we want that field to be unique..We are not able to declare
as a primary key..Is there anyway to declare as unique from the database
point of view?..please help us out in this issue and i'd appreciate that.
Andrew.
Hi,
I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc.
I tried to find it out but it seems to be very complicated.
Could anybody help me in this regards?
Thanks in advance.
Prasanna.