Identity Column in SQL SERVER.

[gandhi]

Hello Guru's,

In SQL Server we have an option called "Identity" when we define the data type. This will automatically generate the Sequence numbers. And then, if you update the existing value with a deleted one, it will generate an error.

This is quite usefull in many applications.

Like this do we have any options in ORACLE. The powerfull database on the Earth.


eg:(In SQL Server)
1.select * from t;
1 abc
2 xyz
3 pqr

2.Delete from t where no=2;

3.update t set no=2 where no=3;

Error occurs.

Can we have this Oracle.


Thanks in Advance


gandhi
OCP8-DBA

[TimHall]

Hi.

You can have the same thing in Oracle but you have to do it manually by doing something like:

http://www.oracle-base.com/Articles/Misc/AutoNumber.asp

That's how I do it if I'm working with people who are used to SQL Server orMS Access.

Cheers

[Edited by TimHall on 05-14-2002 at 06:03 AM]

[balajiyes]

hi tim
if iam wrong corect me

this want i have did. but gandhi wants to prevent the users from updateing or deleting the records
-----------------------------

1 INSERT INTO tim (description)
2* VALUES ('My Description2')
16:35:39 secdev >/

1 row created.

16:35:40 secdev >commit;

Commit complete.

16:35:42 secdev >SELECT * FROM tim;

ID DESCRIPTION
---------- --------------------------------------------------
1 My Description
2 My Description2

16:35:46 secdev >delete tim where id =2;

1 row deleted.

16:35:56 secdev >commit;

Commit complete.

16:35:58 secdev > INSERT INTO tim (description)
16:36:19 2 VALUES ('My Description2');

1 row created.

16:36:20 secdev >commit;

Commit complete.

16:36:22 secdev >SELECT * FROM tim;

ID DESCRIPTION
---------- --------------------------------------------------
1 My Description
3 My Description2

16:36:24 secdev >update tim set id = 2 where id = 3;

1 row updated.

16:36:39 secdev >commit;

Commit complete.

---------- --------------------------------------------------


thnx

[TimHall]

Sorry, I missed the bit about the update since I was concentrating on the automatic nature of the Identity column.

I usually do this by something like this:

UPDATE departments
SET description = 'New Description'
WHERE id = 2;

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'Specified department does not exist');
END IF;

I suppose you could build this into a trigger??

Cheers