CODEWITHSUNDEEP
oracle11gprimary-key
user3044431
user3044431

Reputation:

Trying to auto-increment in Oracle 11g

I am trying to create an auto-incrementing primary key for a table but Im having no luck. This is in Oracle 11g and I am seriously missing the MySQL auto-increment command. PrimkeyID is the column I am trying to use as the primary key. This is a intersect table for a many to many relationship. At any rate, the error says that I am missing a key word just within the beginning of the primary key parenthesis. Also I don't have the privilege level required to do triggers, which appear to be important for incrementing in Oracle.

create table SITE_JUNC
(
primkeyID number,
FKsuperpave varchar(30),
FKcont_mix varchar(30),
)
;
alter table site_junc 
add constraint primary key(create sequence primkeyID incement by 1),

add constraint FKsuperpave foreign key(mix_id_superpave)
references SMGR_CONT_MIX(ContMix),

add constraint FKcont_mix foreign key(mix_id_cont_mix)
references SUPERPAVE(SuperMix)

Upvotes: 1

Views: 1066

Answers (1)

Renato Afonso
Renato Afonso

Reputation: 654

First, sequences are objects disassociated from the table. You have to first create the sequence, and THEN the table (not necessarily in that order).

Create sequence seq_table;

Then, at the insert statement, use the seq_table.nextval (or by using triggers). If you don't like this solution, you can use GUID (i personally don't like it) :

create table SITE_JUNC
(primkey number RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
....)

Starting Oracle 12c, you have Identity columns.

Upvotes: 1

Related Questions