string as an additional primary key

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

General SQL Server Forums

Database Design and Application Architecture

string as an additional primary key

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-06 : 16:16:42

Hi,
I'm creating this table for contract numbers (which are unique strings)
I'm using Sql 2005 express (eventually will be migrated to Sql 2005 Enterprise)


CREATE TABLE [dbo].[Contracts](
	[ConID] [int] IDENTITY(1,1) NOT NULL,
	[ProgramID] [int] NOT NULL,
	[ProjID] [int] NOT NULL,
	[ContractNum] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PoPStart] [datetime] NOT NULL,
	[PoPEnd] [datetime] NOT NULL,
 CONSTRAINT [PK_Contracts] PRIMARY KEY CLUSTERED

I'm fairly certain that I can't afford to have another record with the same contract number.
So should I make ContractNum into a unique PK (saving myself some work) or should I do something else (like lots of error checking in my stored procedure)?
will making a string a PK be too much of a performance or design issue?
Please advise,
Thanks

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-06 : 16:39:28

You can just create a unique constraint on that column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-06 : 16:52:02

Thanks Tara,
what's the syntax? do i just add the word UNIQUE to the column creation statement?
Sorry I can't seem to find the syntax...

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-06 : 16:54:36

It's the same syntax as the PK part of your script. Here's the constraint syntax for CREATE TABLE:

< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
{ ( column [ ,...n ] ) }
]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
}

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-04-06 : 17:16:03

Thanks Tara!
You're awesome!
Here's what I did after you straightened me out:
(I couldn't do it through the GUI for some reason so I did it the old fashioned way)


ALTER TABLE [dbo].[Contracts]  WITH CHECK add constraint check_contract UNIQUE (ContractNum)

It worked perfectly
Thanks again

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-06 : 17:35:13

No problem, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources