Trouble determining a proper PK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trouble determining a proper PK

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-07 : 14:59:51

I have a table that lists business information (name, address, phone, etc.). In this table there are approx 32,000 records and each one was imported from an Excel spreadsheet. Since many of the records had null values in one or more columns, I'm having a hard time setting any one particular column as a PK. I have an identity column, but I'm aware that using identity for PK is a big no-no.

I've considered adding a column or two in which I could contrive a unique value -- say, the first 4 characters of the biz name + phone number prefix + integer biz category -- but I have the feeling it would make the actual data unmeaningful. (I don't even know if I'm making sense here.)

There are many other processes that will (eventually) depend on the uniqueness of this table so I'm trying to get it right.

Any suggestions?

Thanks,
Brian


CREATE TABLE [dbo].[BusinessList] (
	[RowID] int IDENTITY(1001,1),
	[BizName] [varchar] (128) DEFAULT 'none' NULL ,
	[MailingAddress] [varchar] (128) DEFAULT 'none' NULL ,
	[MailingCity] [varchar] (64) DEFAULT 'none' NULL ,
	[MailingState] [char] (2) DEFAULT 'ZZ' NULL ,
	[MailingZIP] [varchar] (10) DEFAULT 'none' NULL ,
	[Phone] [varchar] (12) DEFAULT 'none' NULL ,
	[Fax] [varchar] (12) DEFAULT 'none' NULL ,
	[SalesVolume] [varchar] (64) DEFAULT 'none' NULL ,
	[EmployeeCount] [varchar] (64) DEFAULT 'none' NULL ,
	[MainContactFirst] [varchar] (32) DEFAULT 'none' NULL ,
	[MainContactLast] [varchar] (64) DEFAULT 'none' NULL ,
	[MainContactTitle] [varchar] (64) DEFAULT 'none' NULL ,
	[SICCodePri] [char] (4) DEFAULT '0000' NULL,          -- never null, 2,200 unique codes
	[SICCodeSec] [char] (4) DEFAULT '0000' NULL,
	[SICName] [nvarchar] (256) DEFAULT 'none' NULL ,
) ON [PRIMARY]

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-07 : 15:18:34

>> using identity for PK is a big no-no.
Not eally - just for no reason.

If this is imported then maybe there isn't a natural PK. Try massaging the data to make it more reliable e.g. get rid of the nullable fields and see if there is anything obvious.

Consider separating the Id for the record from the address (have a table for all addresses not just for this table) - and definitely look at putting contacts on a seperate table - maybe just leaving a link to the main one.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-07 : 15:53:00

quote:
Originally posted by nr

>> using identity for PK is a big no-no.
Not eally - just for no reason.

If this is imported then maybe there isn't a natural PK. Try massaging the data to make it more reliable e.g. get rid of the nullable fields and see if there is anything obvious.

Consider separating the Id for the record from the address (have a table for all addresses not just for this table) - and definitely look at putting contacts on a seperate table - maybe just leaving a link to the main one.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-03-07 : 15:54:58

Well, I had a nice response all typed out but got lost when I submitted without a password.

I got what you were saying Nigel. Thanks for the suggestion. I'll split into 3 or four tables to make the main table more unique.

-Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources