SQL / sqlplus - AutoNumber with IDENTITY

[Matt84]

I am stuck with SQL. I must admit that I am just starting to learn SQL. As a part of my uni assignment I have to create an ID collum that contains an autonumber. I've searched teh web and found IDENTITY but Im not sure if i've used it right. I get a "missing right parethesis" error with a star under the I.

CREATE TABLE COMPLAINT (
ID INTEGER IDENTITY(0,1),
DIAGNOSIS VARCHAR(40) NOT NULL,
MED_NUM CHAR(4) NOT NULL,
MED_SEQ CHAR(2) NOT NULL,
CONSTRAINT COMPK
PRIMARY KEY(ID),
CONSTRAINT COMFK
FOREIGN KEY(MED_NUM,MED_SEQ) REFERENCES PATIENT (MED_NUM,MED_SEQ)
);

Also i need to created a categorical list and that is giving me the same error as IDENTITY.

CREATE TABLE DRUG (
PKG_NAME VARCHAR(30) NOT NULL,
TECH_NAME VARCHAR(40) NOT NULL,
BATCH_NUM CHAR(7) NOT NULL,
FORM CATEGORICAL(2) VALUE LABELS ('T' 'TABLET','M' 'MIXTURE'),
DOSAGE INTEGER,
QUANTITY INTEGER NOT NULL,
CONSTRAINT DRUPK
PRIMARY KEY(BATCH_NUM)
);


Any help will be greatly appreciated

 

[bunker]

Which database are you using? Microsoft, Oracle, MySQL, etc...

Edit: Nevermind, I just noticed the sqlplus in your subject so I assume Oracle. I'm searching for ya 🙂.

Okay, I use MS SQL so it's super easy to do an autonumber column, never used Oracle and it sounds like a pain in the ass to do.

http://www.jlcomp.demon.co.uk/faq/autonumb.html

 

[Matt84]

cheers for that

many thanx, now i have a chance at passing my Database Systems topic at Uni. This assignments work 50% of my grade / exams the other 50%

 

[Haircut]

You don't need to do the whole thing of creating a trigger as is done in the link that bunker posted.

Once you have created your table then just create a sequence that you can use to generate a unique sequence of numbers.
This is done in the following way:

CREATE SEQUENCE complaint_s;

You can use the optional START WITH and INCREMENT BY clauses to give a bit more flexibility in your numbering.

e.g.
CREATE SEQUENCE complaint_s
START WITH 100
INCREMENT BY 100;

would give you 100,200,300,etc..

Now, when you insert into your complaint table you would do it in the following way.

INSERT INTO complaint
(id
,diagnosis
,med_num
,med_seq
)
values
(complaint_s.nextval
,'chosen diagnosis'
,'med_num'
,'med_seq'
);

Assuming the database column supports it then you can treat the sequence next value as any other variable, hence it would be possible to do your insert using the following:
'C'||complaint_s.nextval, thus giving you C1, C2, C3, etc.. as your primary key.

I should point out that using a sequence is not guaranteed to give you a contiguous set of ids as if the user begins a transaction in which an insert is done using a sequence and then rolls back the sequence does not rollback (in a multiuser environment it would be pretty much impossible to roll the sequence back anyhow), so you would be left with a gap in your numbering.