How to auto increment with character + integer?

Hi,

Can anyone help me? I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.

Here is my question, how can I do in the following format:

"PIC0001", "PIC0002", "PIC0003", ......... etc.

Anyone can help me?

Thanks.You can create a calculated field as part of a view, using the number from the IDENTITY column appended to the string "PIC".

SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||i'm a rookie...can u explain more detail for this one pls? SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID

'PIC' is the literal you apparently want at the beginning of the string. ID would be a column of type INT, set to IDENTITY. REPLACE(STR(ID,4),' ','0') takes an int number, like 12, and changes it to '0012', thus for a row with an ID of 12, you would get a column called AlphaID with a value of 'PIC0012'.|||hello
thank you for the support

you mean that i have to make two fields one called ID of type int and the other called alphaid
of type nchar and put the default value of this field as you say?

or i have to put the statment SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID in the code of the application?

thank you again for the help|||If the prefix is ALWAYS 'PIC', then I would just create a VIEW that has as part of it that SELECT statement. If it varies, then I would create the table with the ID field and the Prefix field, and then combine them in a view.|||You could also implement this as a computed column within the tabl itself.

This approach might add more overhead though, so it depends on how many of your selects would actually be accessing this field directly.

One potential advantage to having the value as a computed column is that, assuming the computed column meets certain criteria, you can index the column. If you are planning on retrieving rows based on this field, this could work to your advantage.|||Actually, given triggers, you could certainly create a "real" field to hold the value and have it populated in the trigger.|||But would you gain anything by going the trigger route? Wouldn't that just add overhead to your inserts? Or would the overhead be comparable to the overhead assiociated with a computed column (at least when maintaining an index on that computed column).

To my mind, it is a LOT easier to configure a computed column for this solution than it is to program a trigger. But, then again, I rarely use triggers, so I might not be the best resource here. All things being equal though (and I'm NOT saying they are; you might have a better idea than me on that one), I'd go with the solution that's easier to implement.|||Presuming the ID number part of the computed column never changes, you would need only an INSERT trigger. In most applications, the number of INSERTs vs. the number of SELECTs is very small, and so the overhead of doing the insert trigger is really small.

I certainly agree that often easier is better. I have not compared performance of these two solutions (though I would likely do a column fed by a trigger).|||I might suggest you use IDENTITY as PK and add 'PIC' and '000..' to the layer(s) that require it.

This is based on the presumption that PIC000n... is a (surrogate) PK with no "real" business meaning and using only int will make the key more efficient.|||I agree, to an extent. If multiple systems, or layers, might be touching this data, then it CAN add some consistency to be able to define this formatting in one central location.

While you could easily add this computed "key" as a property of a class, for example, you might then need to write numerous report which use this key; some might be Crystal reports, some SQL Server reporting services. Some might be generated ad hoc by the end user. IF this is a business-level way of identifying these records, I don't necessarily see a problem with allowing that identifying characteristic to be stored with the record itself.|||hello
thank you for the support
but can you give me more details about how can i do that?

thank you again for th help anf for the time|||Hi,
I have similar question, I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.
Here is my question, how can I do in the following format:
"1", "2", "3", ......... etc.
Anyone can help me?
Thanks.|||If you just need numbers to autoincrement, set the column to be an IDENTITY column.

How to auto increment an alphanumeric Primary Key in SQL? :(

How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue?

T___T I hope u get my point.. I want my table to look like this if i have added the data

ABC-0001
ABC-0002
DEF-0001
DEF-0002
AAA-0001

then if il add a new 1, for example ABC

it will auto increment to 0003 in the same field, it will look like this after adding

ABC-0001
ABC-0002
ABC-0003
DEF-0001
DEF-0002
AAA-0001

Will it be possible? :(

hi,

i got wat you wanted to say.but i think auotincrement is possible only with numeric fields,i cant think of any possible algorithm that would solve your problem for autoincrementing alphanumeric field.But one possible solution would be to generate your own primary key.For that to happen first you must split your Alpha-numeric field into two..ie first part will take ABC and second would take ur numeric field 0001 etc...

make your primary key as the combination of both the fields...this is the solution i could think of...

hope you could find,wat you actually wanted..all the best :)

|||

Hey ranjit that's what im thinking off also... bat what if there's a new alpha combination, for example DEF, it will continue to autoincrement, and i think it will not return to 0001.. so what will happen is, if i have added ABC-0001 and ABC-0002, the next one would be
DEF-0003 not DEF-0001

will it be possible to put it back to 0001 if il add a new alpha combination? or very impossible? :(

T___T

|||

to get the primary key... you have to findmax for that perticular string with Query sequence like...

SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHEREID LIKE 'DEF%' [this can be like ..."id like 'ABC%' to get highest value in ABC Sequence].. hope it helps you..

|||

wow thanks for this kaushal.. il try it out :)

How To Auto Increment A Primary ID Field?

I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?

For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input......I'm very sorry but I'm having a hard time putting this into words for some reason..

umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?

Thanks

Adam.

On the ID column set identity=Yes, and the value will be assigned automatically!

HTH

|||OH MAN your joking right? LOL I must have overlooked something.....because at first I thought that was how you do it but I was not sure and I think I tried setting it to yes in the past but I must have overlooked the values that changed or something....maybe I was having an off day.....but thats perfect thank you so much...|||Actually sorry to bother you again by any change would you know if you can set a primary key and a foreign key inside the same table?|||

This is why I needed my foregn key and primary key things to work...well at least my search's that I do point me in that direction.

I have a couple of listboxs where a user clicks on an option in parent listbox and the results come out on the child list box

Pritty much my problem is that when I use one table and several columbs the first ListBox (ListBox1) which only contained 6 Items had about 30 more Blank (Or Database NULL) fields after the 6 Selections....This was due because some fields were longer then others......The only way I've got around this is to create a different sql datasource for all selections which gives me 7 total sqldatasource on one particular page.....not a very smart coding practise lol...

How to assign primary key or forign key to existing tables

Hi,

I am new to SQL server. I have one doubt.

I am using SQL server 2005 Express Edition

I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George

Quote:

Originally Posted by sijugeo

Hi,

I am new to SQL server. I have one doubt.

I am using SQL server 2005 Express Edition

I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George

Hi
You can do by searching in sql books online.
Ok......add primary key constraint on one column in one table using alter table add constraint.add refference constraint on one column in other table
example:
[code]

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE doc_exe ADD

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE doc_exe ;
GOsql

How to Assign Identity Key values in Replication

This is a very basic question on replication.

I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.

Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.

Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-

1. I don't want to use GUID.

2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.

i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.

any help will be highly appreciable.

You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||

thanks for your reply.

i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..

is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.

How to alter the table with delete/update cascade without recreating the table

I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.

Thanks

************************************************** ******
Contract table DDL is

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/7_design_41.htm for detailed explanation.


Also refer to books online for additional information.|||guys,
the earlier response was not helping. Does anyone have better help?
I tryed the new DDL but not working either. How can I alter my table or create new one to make the cascading working.

thanks in advance!

************************************************** *
My original contract table DDL is

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId)
contactId int not null foreign key references contact (contactId)
);

************************************************** ***
My modified contract table DDL is

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key
references student (studentId)
on update cascade
on delete cascade,
contactId int not null foreign key
references contact (contactId)
on update cascade
on delete cascade
);|||set nocount on
begin tran
if object_id('dbo.t1') is not null drop table dbo.t1
go
if object_id('dbo.t2') is not null drop table dbo.t2
go
create table dbo.t1 (f1 int not null primary key, f2 char(1) null)
go
create table dbo.t2 (f0 int not null primary key, f1 int not null constraint fk_t2 foreign key references dbo.t1(f1))
go
insert t1 select 1, 'A'
go
insert t2 select 1, f1 from t1
go
select * from t2
go
alter table t2 drop constraint fk_t2
go
alter table t2 add constraint fk_t2 foreign key (f1) references t1(f1) on update cascade
go
update t1 set f1 = 2
go
select * from t2
go
rollback tran
go
set nocount off

How to add the NOT NULL and PRIMAY KEY Constraints with ALTER command?

Dear All,

I wanted to alter a column in a table for adding both NOT NULL and PRIMARY KEY constraints to an existing column. How do I go about that.

I need your help.

First use alter table alter column for redefine column with not null, then use alter table add constraint

Code Snippet

createtable MyTable

(

id intnull,

namevarchar(100)

)

altertable MyTable altercolumn id intnotnull

altertable MyTable addconstraint pk_id primarykey(id)

how to add record with same Primarykey that user deleted

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.An autonumber? Look at IDENTITY_INSERT in BOL.

how to add primary key in existing table

i have table fff .it has two fields one is fno int , another is fname
varchar(20)
fff
fno fname
--- ----
100 suresh
102 ramesh
here there is no not null constraint and identity column then
i am add primary key constraint fno column pls help mesurya (suryaitha@.gmail.com) writes:

Quote:

Originally Posted by

i have table fff .it has two fields one is fno int , another is fname
varchar(20)
fff
fno fname
--- ----
100 suresh
102 ramesh
here there is no not null constraint and identity column then
i am add primary key constraint fno column pls help me

ALTER TABLE fff ADD CONSTRAINT pk_fff PRIMARY KEY (fno)

You find the syntax for ALTER TABLE in Books Online. Yes, it is quite
complex. Then again, there are quite a few examples in that topic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to add Fill Factor in the SQL script

I am trying to set up the relationship (Primary Key and Forign Key) in several tables. I would like to find a way also be able to set 'Fill Factor = 90%' in the script. Here is the code that i have so far:

ALTER TABLE Req
ADD CONSTRAINT FK_Req_Bow FOREIGN KEY ( BowID )
REFERENCES Bow ( BowID )

Any help would be greatly appreciated!

J827FILLFACTOR pertains to an index that you need to create for your FOREGN KEY constraint, not to the constraint itself.

How to add a foreign key constraint using the SQL server 2000 enterprise manager

Hi,

How to add a foreign key constraint using the SQL server 2000
enterprise manager?
Not by SQL.

thanks"Mark" <mchung_pc@.hotmail.com> wrote in message
news:1117734724.479577.321970@.o13g2000cwo.googlegr oups.com...
> Hi,
> How to add a foreign key constraint using the SQL server 2000
> enterprise manager?
> Not by SQL.
> thanks

I think you have to create a database diagram, then manage relationships in
that somehow, but I don't really know - EM is not a very good tool for
database design or programming. I would do it in Query Analyzer, because
then you have complete control over what you're doing and you also have a
script you can save, put under version control, run against multiple
databases etc.

Simon|||Many thanks.

regards|||Actually, this is NOT a very complicated procedure.
Assume table1.custID = table2.custID.

In EM, open table1 in design mode. Click the menu bar icon for "Manage
Relationships". Click the "Relationships" tab. Click the "New" button.

Now you can select keys/fields from the tables. I'm sure you can figure
it out from there.

ldh

Simon Hayes wrote:
> "Mark" <mchung_pc@.hotmail.com> wrote in message
> news:1117734724.479577.321970@.o13g2000cwo.googlegr oups.com...
>>Hi,
>>
>>How to add a foreign key constraint using the SQL server 2000
>>enterprise manager?
>>Not by SQL.
>>
>>thanks
>>
>
> I think you have to create a database diagram, then manage relationships in
> that somehow, but I don't really know - EM is not a very good tool for
> database design or programming. I would do it in Query Analyzer, because
> then you have complete control over what you're doing and you also have a
> script you can save, put under version control, run against multiple
> databases etc.
> Simon
>