Identity -&> PrimaryKey
Oct 29, 2007
I've a question. I've an old db here with no Primary keys nearly but a lot of identity's set, one for each table (nearly). I'm wondering, could something somehow go wrong with some sp's or anything if I make primary keys of these identities? Question why I'm asking is that we're takinging about doing replication but can't since there are primary keys lacking but we dont dare to change ém into primary keys if something might go wrong since we dont have knowledge about that.
Thanks for help.
View 9 Replies
ADVERTISEMENT
May 15, 2008
Hi,
Got a question. I have a script that create a table-- I need Col1, Col2 and Col3 to have PrimaryKey. How do u do. Here is the script
CREATE TABLE [dbo].[PROC_DATA] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Col3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date1] [datetime] NOT NULL ,
[Recod1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [DRI_CHASE_DATA_GRP1]
GO
Josephine
View 5 Replies
View Related
Dec 1, 2005
DATETIME AS PrimaryKey?
Hi Dears!
declare @dtpk smalldatetime
select @dtpk=getdate()
print @dtpk
in the above, i m using @dtpk as a primary key value in a
table to keep a track of some events that are occuring with
a difference of one second. the above method gives me value
to store in the Primary key Filed (EventTime smalldatetime)
in the form "Dec 1 2005 7:57PM" where my requirement is
to save it in the form "Dec 1 2005 7:57:12.03PM" so as to
make it unique for the primary key field(EventTime
smalldatetime). how to get required datetime format with
the help of above statements to store as primary key in
EventTime field of type smalldatetime.
THNX IN ADAVNCE
View 10 Replies
View Related
Aug 10, 2007
Hello
I've taken over a database where for many tables the postcode field (equivilent to the US Zip Code) is the obvious primary key. [Mapping postcodes to distict or various types of area for statistical analysis]. However the people who set up the database have continually used an auto generated number for the primary key.
There are never any duplicate postcodes in the various tables, so my question is what is the advantage of either using or not using the postcodes as primary keys.
View 2 Replies
View Related
Mar 15, 2007
I want to know if this primary key behavior is unique to Access or if it is THE WAY that all industry-standard primary keys work. Thus, I hope not to get too messed up on my SQL Server 2005 project--or any other SQL project.
In Access, I set up a table 'tbl_Gen_Admin_sto' to store general administration expenses. This is a budget. So, there can be at most one entry per budget_year / cost_center / account_number / month. If there are two or more entries, the data is messed up, something is wrong.
Therefore, I defined a muliple-field primary key for tbl_Gen_Admin_sto. If I make a programming mistake and enter data multiple times, the primary key feature will stop my program from entering the same data multiple times.
As I often do, I copy the data into another table, in this case tbl_Gen_Admin_tmp for manipulation in a form. (I.e., I connect a form to tbl_Gen_Admin_tmp.) The tmp version is a pivoted version of the sto table. So, in sto, I have one amount field for each month. In tmp, I have no month field, but do have one amount field for every month of the year (12 amount fields).
The transfer of data to tmp went fine. The user adds, changes, and deletes records in tmp through the form. When the user is done and wants to save the changes (and not discard changes), my program deletes the selected records from sto, then inserts the records from tmp.
But lo! Access was not accepting the new records because (error message) "key violations". The delete went fine. But the insert would not take.
I deleted the multiple-field primary key index in sto. Then I created a multiple-field regular index with no duplicates allowed. Now, sto accepts the insert.
I thought that when I deleted the records, the primary key field is gone--zzzzpft! But aparently Access is keeping track of all primary keys. Even though I deleted the record, for all eternity I will never ever be able to re-insert with the same primary key.
Here is where I need your help: Am I going to run into this same primary key behavior in SQL Server 2005? If so, is it because I have encountered the fundamental definition of primary key?
(I hope you don't beat me up too much for abusing primary keys.)
View 6 Replies
View Related
Aug 19, 2007
Hi, I having a problem with my query...
I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...
I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...
What i'm doing is like this:
Use osa (Destination Database)
Go
DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)
My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.
How is this possible sir? Anyone care to help? Thanks in advance! More Power!
Best Regards
View 10 Replies
View Related
Jan 15, 2007
Hey,
I have a page that inserts into a customers table in the DataBase a new customer account using this function:
Public Function InsertCustomers(ByRef sessionid, ByVal email, ByVal pass, Optional ByVal fname = "", Optional ByVal lname = "", Optional ByVal company = "", Optional ByVal pobox = "", Optional ByVal add1 = "", Optional ByVal add2 = "", Optional ByVal city = "", Optional ByVal state = "", Optional ByVal postalcode = "", Optional ByVal country = 0, Optional ByVal tel = "")
Dim result As New DataSet
Dim tempid As Integer
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("Conn"))
Dim Adcust As New SqlDataAdapter
Adcust.InsertCommand = New SqlCommand
Adcust.SelectCommand = New SqlCommand
Adcust.InsertCommand.Connection = conn
Adcust.SelectCommand.Connection = conn
sessionExists(email, sessionid, 1)
conn.Open()
If fname = "" Then
Adcust.InsertCommand.CommandText = "Insert Into neelwafu.customers(email,password,sessionid) Values('" & email & "','" & pass & "','" & sessionid & "')"
Else
Dim strsql As String
strsql = "Insert Into neelwafu.customers"
strsql = strsql & "(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values("
strsql = strsql & "'" & sessionid & "','" & email & "','" & pass & "','" & fname & "','" & lname & "','" & company & "','" & pobox & "','" & add1 & "','" & add2 & "','" & city & "','" & state & "','" & postalcode & "', " & country & ",'" & tel & "')"
Adcust.InsertCommand.CommandText = strsql
End If
Adcust.InsertCommand.ExecuteNonQuery()
Adcust.SelectCommand.CommandText = "Select Max(id) from neelwafu.Customers"
tempid = CInt(Adcust.SelectCommand.ExecuteScalar())
conn.Close()
Return tempid
End Function
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now, I am getting an error:
Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The customers table has as a primary key the 'email'.....
so plz can I know why am I getting this error ????
Thank you in advance
Hiba
View 4 Replies
View Related
Feb 28, 2007
I have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?
Many thanks.
View 1 Replies
View Related
May 13, 2008
Hi,
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Thanks in advance.
ramesh.p
View 1 Replies
View Related
Jul 9, 2006
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
View 10 Replies
View Related
Jun 30, 2006
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?
View 12 Replies
View Related
Jan 25, 2015
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
View 6 Replies
View Related
Sep 19, 2005
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Aug 1, 2014
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
View 4 Replies
View Related
Aug 12, 2009
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies
View Related
Oct 26, 2006
i have found loads of topics on this but have yet to find one that gives the answer i need.I want to display the last insert id into a label/textbox after the INSERT functionhow do i do this?
View 18 Replies
View Related
Feb 29, 2008
I got two tables
one table has the fields ie table1
orderid ofd orderdate customername
where order id is autonumber
the other table2
orderid ofd product id productname
the problem here is thatif customer purchases 3 product at a time all the 3 products get the same ofd number ........and any 2 customers can have the same ofd number................ now i have to pull the order ID value from table 1 to table 2............ can somebody help with this i am the front end is asp.net amd the database is done on SQL server management studio
View 1 Replies
View Related
Mar 3, 2008
Hi, I was looking through this thread about @@Identity: http://forums.asp.net/p/1039145/1443971.aspx#1443971 I'm still unsure how to ue it. I have an Orders table, a Products table and a Products_Orders table. When I add an order to the Orders table I want the PK OrderID in this table to also update the FK OrderID in my Products_Orders table. I'm using SQL EXP05 and I'm in C#.
View 1 Replies
View Related
Mar 11, 2008
Hi i want to get the naxt identity number how can i get it pls help me very urgent
View 3 Replies
View Related
Feb 24, 2004
I know identity key in a table can cause problems when the table is replicated. Should we avoid using identity key altogether, if we don't know in advance whether replication will come into the picture?
Thanks for any advice.
View 2 Replies
View Related
Aug 30, 2004
hello,
I've got a problem reading the @@identity in vb.net
I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found.
(--> means with rsLastIdent)
comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @@IDENTITY AS Ident;"
comm = new SqlCommand(comm_user, dbConnection)
dbConnection.Open()
Try
rsLastIdent = comm.ExecuteNonQuery()
Catch ex As Exception
Response.Write("Exception:")
Response.Write(ex.ToString)
End Try
if NOT rsLastIdent.EOF then
feed_userID = rsLastIdent.Fields.Item("Ident").Value
end if
The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...
Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @@identity?
Thanks a lot!
tim
View 2 Replies
View Related
Sep 20, 2004
hi,
I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:
SELECT
@OrderID = @@Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
OrderID,
ProductID,
Quantity,
UnitCost
)
SELECT
@OrderID,
ShoppingCart.ProductID,
ShoppingCart.Quantity,
Prices.UnitCost
FROM
ShoppingCart INNER JOIN
Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
CartID = @CartID
is there any way to rewrite this statement so that I can put it in the form insert()values(). ?
View 5 Replies
View Related
Jun 17, 2005
HiTrying to get a return value from this code, but only gets a 0. Am using SQLExpress.SqlParameter[] p = new SqlParameter[4];p[0] = new SqlParameter("@a", "aaa");p[1] = new SqlParameter("@b", "bbb");p[2] = new SqlParameter("@c", "ccc");p[3] = new SqlParameter("@d", SqlDbType.Int, 40);p[3].Direction = ParameterDirection.ReturnValue;
string s = @"set nocount on INSERT INTO ABC(A, B, C) VALUES(@a,@b,@c) SELECT scope_identity()";
using(SqlConnection conn = new SqlConnection(this._connection)){ conn.Open(); SqlHelper.ExecuteNonQuery(conn, CommandType.Text, s, p); int foo = p[3].Value;}
View 4 Replies
View Related
Jul 20, 2005
Hi all,
is there a way in SQL Server for me to get the next available Identity value from an Identity column?
(I don't want to perform an insert, and then get the identity through @@IDENTITY!)
Thanks
Tryst
View 4 Replies
View Related
Jun 1, 2006
I am trying to follow other examples I have seen on the site, and am still getting the
Must declare the scalar variable "@@INDENTITY".
string sqlAdd = string.Format("INSERT INTO " + siteCode + "_campaign_table (campaign_name, prod_id, type) "
+ "VALUES('{0}', '{1}', '{2}'); SELECT @@INDENTITY", campaignName, prodID, type);
SqlCommand comAdd = new SqlCommand(sqlAdd, con);
comAdd.CommandType = CommandType.Text;
con.Open();
//comAdd.ExecuteNonQuery();
int identity;
identity = Decimal.ToInt32((decimal)comAdd.ExecuteScalar());
lblErrorMessageAdd.Text = identity.ToString();
con.Close();
View 3 Replies
View Related
Nov 5, 2001
Is it possible to use the ALTER TABLE statment when modifying an INT field to be an IDENTITY.
thanks in advance.
View 4 Replies
View Related
Jul 11, 2000
I would like to know the best way to select/maintain a sequence number in SQL Server. I've seen locking problems with using the @@identity and was wondering if there is a better way.
Several of our applications have the need to generate a sequence number that is inserted into one or several tables. In one application they have done the following ...
- Created a table with a column defined with identity attribute, for example
TableA
ColA defined as Int with Identity checked
ColB define as char(20)
- In the application, code looks like to get the sequence number:
insert into tableA (ColB, 'anything');
select (@@identity) as sequence from TableA
Then the last 5 positions of sequence are used to insert into another table. Problem with this is that several rows are being created in tableA when only a sequence number is needed. Also, we need to make sure no one else does an insert before the select @@identity.
Another approach I'm thinking about would be to create a one row table that contains an integer field initialized with a value of 1. To select/update the sequence number the code would need to:
set transaction serializable
select number
from tableA UPDLOCK
update tableA
set number = number + 1
How are most people generating a sequence number in SQL Server? In Oracle this would be done by selecting sequence.nextval. For example:
Select sequenceA.nextval from dual;
Is there an equivalent way in SQL Server 7.0? Thanks.
View 2 Replies
View Related
Oct 17, 2000
Hi,
Is there a way, I can give access(not dbo or sa) to a person so they can bcp into tables that have identity columns? I want to be able to give permissions ahead so I do not have to bother setting the identity insert on every time he wants to bcp.
Thanks a lot for your help
Sasha
View 3 Replies
View Related
Nov 30, 1999
I'm successfully inserting into a db using a stored proc, but I need to replicate the ClientID to 10 other tables. For some reason, this one is escaping me.
After a successful insert, I then use:
SELECT @@IDENTITY ClientID
SELECT @ClientID = @@IDENTITY
INSERT INTO table2
(ClientID)
VALUES(@ClientID)
INSERT INTO table3
(ClientID)
VALUES(@ClientID)
INSERT INTO table4
(ClientID)
VALUES(@ClientID)
The insert ClientID into the other tables is being inserted. I added SET @ClientID = @@IDENTITY and it made no difference.
Thanks for the help.
Alan
View 1 Replies
View Related
Sep 2, 1999
Does anyone know if there is bug/problem with the @@identity global variable in SQL Server 7.0?
I have a stored procedure that inserts a row into a table with an identity column and returns (outputs) the value
of the identity column just generated. The SP is called by a Java program. The SP works fine most of the time,
however from time to time it retuns a NULL value! Your comments/suggestions are much appreciated.
View 1 Replies
View Related
Apr 18, 2000
Hi everyone,
Can someone explain how I can use another table to hold my @@identity value so I can have the correct value to update other tables?
Thank you very much,
Nathan
View 6 Replies
View Related
Mar 9, 2001
I have a table (tblMoney). When I insert a new row into the table, via a standard insert stored procedure, the new row, has the identity column numbered correctly (say 34 for example). At the end of the stored procedure, we return @@IDENTITY for the developers. This will return a much bigger number (1198).
Any idea how these got out of sync, or how they can be fixed??
Thanks!
Trisha
View 3 Replies
View Related
