Reputation:
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
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