Primary key in sql server i.e 'P-1' , 'P-2' ...

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)

Primary key in sql server i.e 'P-1' , 'P-2' ...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-02 : 10:46:50

Muhammad writes "I want to generate a primary key which would be like 'P-1', 'P-2' and so on .I use to create this kind of primary key in oracle by creating sequence and then implement this in trigger .

How can i do this in Sql server 7.0 ?"

aclarke
Posting Yak Master

133 Posts

Posted - 2002-05-02 : 11:08:04

Here's one way:


CREATE TABLE dbo.FancyIDTable
	(
	aNumber int NOT NULL IDENTITY (1, 1),
	ID varchar(10) NULL,
	moocow varchar(50) NULL
	)  ON [PRIMARY]
GO

CREATE TRIGGER MakeCoolID ON FancyIDTable
FOR INSERT
AS

update FancyIDTable
set ID = 'P-' + cast(aNumber as varchar(8))
where ID is NULL

GO

Or if you don't want the aNumber field you could make your trigger do something like


update FancyIDTable
set ID = (select 'P-' + cast((right(max(ID), (len(max(ID)) - 2)) + 1) as varchar(8)) from FancyIDTable)
where ID is NULL

Of course you'd have to add some code to lock your row or something if you did this.

Or you could just add the 'P-' when you do your joins, like:


select a.*
from a left join b on a.someField = 'P-' + cast(b.someField as varchar(8))

Good luck,
- Andrew.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-02 : 15:30:04

Here's an article by Graz on this topic: [url]http://www.sqlteam.com/item.asp?ItemID=1417[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources