Auto-increment key id field help

I have a key field ID (int) for a table. How can I auto incremnt the key so that when I insert a new record I can get a new key. I should not have to know the next available number in my table. What settings do I use for the field.

Thanks in advance
MoonWa

Check out the IDENTITY property.

Auto-Increasement field?

Hi!

I'm using Microsoft SQL Server Management Studio to design a table with two fields:

id (int)

file (text)

I set 'id' to be primary. I try to add a row to this table but it asks me for a custom value for 'id'. I want it simply to auto-assign a uniqe value for it. How to do this please?

In the table designer, set the Identity Specification to Is_Identity = Yes.

Also, I recommend NOT using [ID] as the column name. A good standard is to use the TableName and ID, so a table named MyTable would have it's IDENTITY column named MyTableID.

Auto_increment

Hello my name is Elisa and I have a problem with this line of code:
CREATE TABLE Film
(IdFilm int not null auto_increment PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Personaggi_Principali VARCHAR2(50) NOT NULL,
Personaggi_Secondari VARCHAR2(50) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Critica VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL)
when I execute the result is:
Error: ORA-00922: missing or invalid option.
It's very urgent.
Thank you Elisaauto_increment does not exist in Oracle!

You have to create a sequence:

CREATE SEQUENCE FILM_SEQ;

and then you can either use it during the insert as in:

INSERT INTO Film VALUES (FILM_SEQ.NEXTVAL, ...);

or create a trigger:

CREATE OR REPLACE TRIGGER BI_FILM_TRG
BEFORE INSERT ON FILM FOR EACH ROW
BEGIN
SELECT FILM_SEQ.NEXTVAL INTO :NEW.IdFilm FROM DUAL;
END;
/
NOTE: DO NOT USE IT ON BOTH THE INSERT AND TRIGGER!!!

:cool:|||Thank you very much for your help
Elisa

Auto Primary Key with fill in

I want a table to have an int auto primary key, that will fill in deleted rows.
EG
1
2
4
5
8

The next primary key should be 3 (NOT 9)

This table changes often, so just adding 1 to the last number used, will fast run out of numbers.
The table is accessed by many users, so it can not be manually generated.

Using the IDENTITY property will successfully and correctly manage autonumbering. However, as you noted, deletions will leave gaps. I suggest that you use a datatype sufficient to allow both growth and allow for the deletions. A bigint datatype is a very, very large number -up to 9,223,372,036,854,775,807, I couldn't imagine that you are adding so much data to the database that a bigint would be inadequate.

It would be possible to create a 'homegrown' solution to manage this, but in my experience, with many users, that is a mistake. It would require a lot of table locking and/or data contention, placing unneeded stress on the database.

There are occassionally, real business needs for managing a numbering sequence, for example check numbers. But in those cases, deletions are usually not allowed.

|||Yes and after about 1 year or so when it flips over to 1 again, and starts over writing data.

Its a very nasty bug.

Its happened before.|||

Hi:

Please refer to the following article, and see if it would help with your concern. :-)

http://www.sqlteam.com/item.asp?ItemID=765

Thanks.

|||That changes existing data.

but can be expanded upon, to use a multi sql statement to defrag a database.

but then how to reset the auto number to the next number.

So far the only way, i can do what i want, is one of two ways

1. Run all sql through a service, that can sync me a new auto fill in number.
2. Have unacceptable down time, and run a program to defrag the tables.

Auto Increment

I have a table that is already populated w/o a primary key. Now I want to have a column that is an int starting at 1 and increments by 1. How do I do this?

Try:

alter table dbo.t1

add sk int not null identity(1, 1) constraint pk_t1 primary key clustered with fillfactor = 85;

go

AMB