How Does Primary Key (primarykey) Work? Can Only Use Once?
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
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
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
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
Jan 11, 2007
Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
View 1 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 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
Feb 4, 2015
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies
View Related
Jan 28, 2004
Hi all
I have the following table
CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
Thanks Matt
View 5 Replies
View Related
Aug 31, 2006
Hello all,I'm taking over a project from another developer and i've run into a bit of a problem. This developer had a bad habit of not using primary keys when designing various databases used by his programs. So now i've got approx 1000 tables all of which do not have primary keys assigned. Does anyone know of a tsql script that i can run that will loop through each table and add a primary key field?Thanks in advance?Richard M.
View 2 Replies
View Related
Aug 16, 2007
I have a Department Table.
Can any one tell me its Primary Key.
I have the order
AutoNumber, D + AutoNumber, Code,
Can you help me regarding this.
Because some people never like to use AutoNumber.
That's why I am confused.
View 3 Replies
View Related
Nov 8, 2007
Hi..
I'm going to build database of university, but I have problem with primaru key,
This is the situation:
there are many faculities and each one has many departments,
each department has many courses,
each course has many sections..
The problem:
I want to make those fields in the same table and make the primary key generate from other fields,
(i.e)
I want the faculity be integer from 4 digit "Example the first faculity start with 1000 the second 2000 and so on" and the the department of each faculity will generate its value from the faculity number+interger number from 3digit "Example the department of the first faculity start with 1100 and the second on will be 1200 and so on "
the same thing will repeate for courses and sections so the sectionsID will be the primary key.
Do you know hoew this idea can be implement by SQL server 2005?
Please help me as soon as possible.
View 13 Replies
View Related
Mar 23, 2005
A column will be Primary Key. Others are B and C. I want A will contain B and C. I mean B data is X, C data is Y, A will be XY. How can i do this? Can i set in MSSQL or need ASP.NET?
View 1 Replies
View Related
Dec 1, 1998
Heya,
I'm trying to setup a Primary Key on a SQL 6.5 database.
Is there a way to do this? When I hit advanced, it asks for me to select a field for the primary key, but it doesnt list fields to selct from, and I cant type it in.
Thanks for your help,
View 3 Replies
View Related
Jul 8, 2004
Hi All,
Using DTS i have imported the data from sybase to MS SQL server and all the data and tables were imported correctly.But the primary keys are not marked why is it like this?
This is not a one time job and this is meant to be for the customers also.I cannot ask the customers to mark the primary keys themselves. Is there a way to get the keys also.While doing DTS I have marked all the options correctly.
Please help.
View 5 Replies
View Related
Sep 23, 2004
I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.
Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.
EG:
-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
----------------------
1 | REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------------------------------------
1 | 1 | 1 | 0.15
-- New way
tbProductionLabour
Labour | fkCostCode
---------------------
REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
-------------------------------------------
1 | REBAR | 1 | 0.15
This is a very basic example, but you get the idea of what I am referring to.
Any thoughts?
Mike
View 11 Replies
View Related
Dec 3, 2004
I need to create my own primary key, how do I go about doing that?? In the database I am working in usually has a primary key that looks like this VL0008
the V is for Vendors, thats basically their number. Some of these Vendors need to be licensed and some dont, the ones that are not licensed dont get a number but I am to use that as the Primary/Index key I need to create one for those particual vendors. How can I go about doing that??? I was wanting to make it TL888 something like that.
View 7 Replies
View Related
Oct 17, 2005
i'm having problem to get th primary key from d database....
for your information i'm using java to get the primary key....
this is my code...
rs = stt.executeQuery("sp_columns "+table_db+";");
while(rs.next())
{
out.write(""+rs.getString("COLUMN_NAME"));
out.write(", "+rs.getString("TYPE_NAME"));
out.write(", "+rs.getString("IS_NULLABLE"));
}
rs = stt.executeQuery("sp_foreignkeys @table_name = N'table_db';");
but the problem is....
i get this error message...could anyone tell me what's the problem....
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
find server 'table_db' in sysservers. Execute sp_addlinkedserver to add th
e server to sysservers.
how do i solve this problem....
thanx to anyone who can help me...... :D
View 7 Replies
View Related
Oct 18, 2006
Please help:
I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo
.... what would be the primary key?! I know it would be DateTo and sumfing...... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"
... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to???
Thanks....
View 14 Replies
View Related
Feb 3, 2008
Hi all,
Is it possible to have a primary key for SQL or Oracle or jet to have an alpanumeric beginning?
for example
1st District as a primary key
The statement is:
SELECT itemid FROM MASecurity WHERE userid=%d
Thanks,
Jj :)
View 14 Replies
View Related
Jan 27, 2004
What 's the way to know
the name of the column that is
the primary key of a table
View 3 Replies
View Related
Mar 11, 2004
In a recent course on database programming using Microsoft Access 2002. I noticed that the text entitiled New Perspectives Microsoft Access 2002 stated that a primary key could only be used once per table. But If I am not mistaken could one use the select key to select more than one primary key within a table.
View 8 Replies
View Related
Apr 14, 2008
Hi guys,
Is there a method in sql server 2005 to format the primary key so it can be alphanumberic?
Thanks.
View 2 Replies
View Related
May 5, 2008
Violation of PRIMARY KEY constraint 'PK_Dunning_TBL'. Cannot insert duplicate key in object 'Dunning_TBL'.
The statement has been terminated.
(0 row(s) affected)
Msg 2627, Level 14, State 1, Procedure GenerateFiles_FST_SP, Line 220
Violation of PRIMARY KEY constraint 'PK_Exceptions_TBL'. Cannot insert duplicate key in object 'Exceptions_TBL'.
The statement has been terminated.
i got this error how can i resolve this?
View 4 Replies
View Related
May 23, 2008
Hi,
I am designing a database for containing the info for the employees in the institution. My dilemma:
-Should I use an sql autonumber primary key?
or
-should I merge lastName+FirstName+middleName into one field and use this string as the primary key?
thank you
View 6 Replies
View Related
Jun 17, 2008
I'm new to these forums, and I'm not a database developer, per se, so please forgive me if I make any newb'ish comments.
I have a lookup table called tblCars, that has two columns, cars_id and cars_title. Typically what I do with tables like this is I make cars_id an autonumber, and cars_title the primary key.
The cars_title would contain unique data such as Ford, Chevy, Toyota, etc, which is why I like to make it the primary key (ie - guarantee that it remains unique and no duplicates are ever placed in it).
I would then create an index on cars_id so that I could use it in foreign key constraints.
However, I'm being told by a number of people that it is incorrect to make cars_title the primary key, and that the autonumber field should be the primary key. Yet I am having trouble arriving at a real good reason as to why this is the correct way to do it. I like the warm-fuzzies that I get knowing that no one can accidentally insert a duplicate car title into tblCars because of the primary key constraint.
Thanks in advance for any thoughts or insights on this.
View 6 Replies
View Related
Feb 2, 2006
I've noticed that some of my tables have primary keys that are not referenced by a foreign key in another table, is this indicative of bad design?
Jill
View 2 Replies
View Related
Nov 8, 2007
Hi..
I'm going to build database of university, but I have problem with primaru key,
This is the situation:
there are many faculities and each one has many departments,
each department has many courses,
each course has many sections..
The problem:
I want to make those fields in the same table and make the primary key generate from other fields,
(i.e)
I want the faculity be integer from 4 digit "Example the first faculity start with 1000 the second 2000 and so on" and the the department of each faculity will generate its value from the faculity number+interger number from 3digit "Example the department of the first faculity start with 1100 and the second on will be 1200 and so on "
the same thing will repeate for courses and sections so the sectionsID will be the primary key.
Do you know hoew this idea can be implement by SQL server 2005?
Please help me as soon as possible.
View 4 Replies
View Related