Surrogate Key 1

[aspiringprog]

Hi all,

I am trying to declare a surrogate key for a table and I get an error saying invalid datatype.

Here is what I am doing. I have created a sequence to generate numbers lets say SEQ.

I am declaring the key as

ID SEQ.nextval in the CREATE TABLE statement and it is giving me an error. I am new to surrogate keys, I've never used them.

Appreciate your help.

Thanks.

 

[lewisp]

You can only use a sequence in a SELECT or part of an INSERT etc.

 

[aspiringprog]

Hi Lewis,

Yes..I realise I will have to create the sequence first and then insert into the table.

However, will I have to insert into the table one row at a time using seq.nextval function? If I wanted to insert multiple rows into the table, then I will have to use a loop i am thinking.

Is there any other way?

appreciate ur help.

 

[lewisp]

There are two ways to insert. One is [tt]INSERT INTO table (column, column,...) VALUES (seq.nextval, value...);[/tt]

Or you can use [tt] INSERT INTO table (column, column...) SELECT seq.nextval, column FROM table...[/tt]

The first example would require a loop but the second will insert as many rows as are found in the SELECT.

 

[taupirho]

From my web page. Hope it helps.

http://the-big-o.port5.com/article17.shtml

 

[aspiringprog]

Thanks to both of you..

I did create a trigger to input the sequence value and it works great..

thanks again