-2

I have a table that has a list of states. I did this to create the table:

CREATE TABLE drb (
  Statename VARCHAR2(15)
)

I then inserted the state data. However, I want my first column to be a created unique state ID. So it should list 1-50 for the 50 states in the United States. I tried searching different unique tutorials like this and nothing seemed to work.

Essentially, I want to know how to get a unique numerical key for a variable

crthompson
  • 15,653
  • 6
  • 58
  • 80
itjcms18
  • 3,993
  • 7
  • 26
  • 45
  • `Id int not null auto increment` – John Ruddell Sep 13 '14 at 06:39
  • You've tagged this for MySQL and Oracle-- two completely different database systems with different SQL dialects. Which of the two are you actually using? – Justin Cave Sep 13 '14 at 06:41
  • possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) – John Ruddell Sep 13 '14 at 06:41
  • hey guys - i'm a beginner at sql. i've gotten good at other languages by teachign myself and asking questions after i feel i've searched my hardest. i put in my post where i searched and i wasn't finding quite what i was looking for. so i went here for help. no reason to downvote, i'm trying to learn. – itjcms18 Sep 13 '14 at 15:29

1 Answers1

0

Your table should have id field along with statename, add it this way.

CREATE TABLE drb (
  id NUMBER,
  Statename VARCHAR2(15)
);

Make id as primary key:

alter table drb
add constraint drb_pk primary key (id);

Create a sequence as follows:

create ore replace sequence drb_sequence
 start with 1 increment by 1;

Create a trigger which will insert a unique id into drb table as follows:

create or replace trigger drb_trigger
before insert on drb
for each row
begin
select drb_sequence.nextval into :new.id from dual;
end;
/

Now insert into drb:

insert into drb(Statename) values('state one');

Select from drb:

select * from drb;

Output :

--------------------------
|id          Statename   |
--------------------------
|1           state one   |
--------------------------

Hope it helps.

Darshan Lila
  • 5,772
  • 2
  • 24
  • 34