Create table wih multi column key 2

[smatthews]

Can someone please help me with the syntax to create a multi column key that will eliminate duplicate records during an import if there is a combined duplicate latitude, longitude, and zip_code?

CREATE TABLE mytable (
ZIP_CODE varchar(10) default NULL,
CITY varchar(50) default NULL,
STATE char(2) default NULL,
LATITUDE decimal(10,8),
LONGITUDE decimal(10,8),
ID INT Identity (1000000000,1)PRIMARY KEY
)

Thanks in advance!
PS. I am still looking through Books-On-Line but hope to get help a little quicker this way... thanks again

 

[donutman]

If that is the complete table, then putting a constraint on the latitude and longitude isn't very helpful. If there is going to be a duplicate city, state and zip, then the further addition of latitude and longitude accurate to 8 decimals isn't likely to be of much value.
What locations are being stored in the table, maybe there's another alternative.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[smatthews]

I'm sorry I meant zip, longitude, and latitude.

 

[checkai]

CREATE TABLE mytable (
  ZIP_CODE varchar(10) default NULL,
  CITY varchar(50) default NULL,
  STATE char(2) default NULL,
  LATITUDE decimal(10,8) NOT NULL, 
  LONGITUDE decimal(10,8) NOT NULL, 
  ID INT Identity (1000000000,1) NOT NULL
)  ON [PRIMARY]
GO
ALTER TABLE mytable ADD CONSTRAINT
PK_mytable PRIMARY KEY CLUSTERED 
 (LATITUDE,
  LONGITUDE,
  ID
 ) ON [PRIMARY]
GO

 

[checkai]

CREATE TABLE mytable (
  ZIP_CODE varchar(10) default NULL,
  CITY varchar(50) default NULL,
  STATE char(2) default NULL,
  LATITUDE decimal(10,8) NOT NULL, 
  LONGITUDE decimal(10,8) NOT NULL, 
  ID INT Identity (1000000000,1) NOT NULL
)  ON [PRIMARY]
GO
ALTER TABLE mytable ADD CONSTRAINT
PK_mytable PRIMARY KEY CLUSTERED 
 (LATITUDE,
  LONGITUDE,
  ZIP
 ) ON [PRIMARY]
GO

 

[checkai]

oops need the not null on zip...

CREATE TABLE mytable (
  ZIP_CODE varchar(10) NOT NULL,
  CITY varchar(50) default NULL,
  STATE char(2) default NULL,
  LATITUDE decimal(10,8) NOT NULL, 
  LONGITUDE decimal(10,8) NOT NULL, 
  ID INT Identity (1000000000,1) NOT NULL
)  ON [PRIMARY]
GO
ALTER TABLE mytable ADD CONSTRAINT
PK_mytable PRIMARY KEY CLUSTERED 
 (LATITUDE,
  LONGITUDE,
  ZIP
 ) ON [PRIMARY]
GO

 

[SQLSister]

Inthe first place, under no circumstances would I use a multi-column key for this. I woudl create an identity field to be the key for related tables becasue a join on a three column field of numeric and character databases would guarantee slow joins and waste space in the related tables.

What you create instead is a unique index. For example:

CREATE UNIQUE INDEX emp_order_ind
   ON order_emp (orderID, employeeID)

Now as to how to import the data. Once you have the unique index most standard imports using the wizard will fail. What I do is bring the new data into a holding table. Then perform the insert to the main table using a left join. Something like:

INSERT Table1 (Field1, Field2, Field3)
Select field1, Fiedl2, Field5 from holding left join table1 
on holding.Field1 = Table1.Field1
and Holding.field2 = Table1.field2 
where table1.field1 is null and table2.field2 is null

Note that when you do this, you do not specify the identity field in the insert (as the holding table will not contain the value for this, it only goes in the real table) and thus must specify all other columns whic you want inthe inserted record (including all field which do not all ow nulls but have no default values). Also you must, just for the insert use the three fields you have that are the unique key in the join. The main reason to avoid using the three column combo as a key is to improve speed after the insert when the records are accessed.

I am rambling on and on here, so I hope I made some sense of the situation for you anyway.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[smatthews]

Thanks! Works great!