Using Postcode As Primarykey

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


ADVERTISEMENT

Substring Of A Postcode

Nov 13, 2007



Hello,

Does anyone have some code that can do the following:

Postcode N12 5FF
Postcode NG4 5FF

I need to get the postcode N12 5FF based on the 'N' bt leave the NG4 5FF postcode behind. So obviously using LEFT will not work, does any have any code that will strip of the first alpha characters?

Thank you.

View 7 Replies View Related

Inserting Space In A Postcode

Jan 30, 2014

We have many postcodes in our database that are UK ones. Some cutomers enter then as one string. Example BA124XT

In the SQL table we have two columns PostCode and Country. Is it possible to write a SQL script that will insert a space three characters from the right of the postcode where the country is UK?

So before it would be BA124XT and after BA12 4XT for them all?

View 5 Replies View Related

Postcode Radius Searching

Jul 20, 2005

Hi - I know this isn't really a specific SQL Server question but Ican't think of a better forum.I want to implement a more sophisticated UK postcode search on a site- "LIKE 'NW%'" etc, is not bringing back good enough results.A feature that would allow me to ask: "give me all outward postcodesin a 30 mile radius of NW10" would be ideal.Has anyone had to do this before? - any advice on how muchappreciated. Some of the postcode products available don't reallyseem to offer this feature.NH

View 2 Replies View Related

Query Postcode Issue (IF Statement)?

Feb 25, 2008

Hi Everyone,

Basically i've got a postcode query problem, i have a column with postcodes like:

abc 123
abcd 123
ab 123

What im trying to do is query only the first set on digits without the 123.

So my output should be:

abc
abcd
ab

Ive used the following technique:

select TRIM(substring(postcode,1,if(instr(postcode," ")=0,length(postcode),instr(postcode," ")))) from dbTest;

This works perfect in MySQL but not in sql enterprise, as it doesnt like the if syntax as well as the instr();

Any solutions please?? any suggestions would be helpful!

Thanks

View 6 Replies View Related

Australian Postcode Db With Longitude/latitude

Jul 23, 2005

not sure exactly where I should ask this question, but im looking fora database of Australian Postcodes that included thelongitude/latitude.Does anyone know of such a thing? Preferably free...fingers crossed.many thanksjack

View 1 Replies View Related

PrimaryKey

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

DATETIME AS PrimaryKey?

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

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 View Related

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 View Related

How To Avoid PrimaryKey Duplication?

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

InsertCommand.ExecuteNonQuery() And Violation Of PrimaryKey

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

How To Add Record With Same Primarykey That User Deleted

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

Is A Foreignkey Column Can Refer Two Primarykey Column From Two Different Tables?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved