Multicolumn Primary key and Creating An Autonumber Field in Oracle

Hey guys...recently i faced this situation where i had to make two columns' combination a primary key in oracle and i found out the solution...

1 ) when creating a table

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

2) when you have to alter table

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

This way you can make two columns's combination a primary key


and now to make a field auto number in oracle...

step 1) create a table

SQL> create table sample (
2 no number not null,
3 description varchar2(200),
4 constraint pk_sample primary key(no)
5 );

Table created.

step 2) Create a sequence object using the following code:

SQL> create sequence seq_sampleno
2 minvalue 1
3 start with 1
4 increment by 1;

Sequence created.

Step 3

Create a BEFORE INSERT trigger on sample table. Why BEFORE INSERT event? Because we need to take a sequence object’s value and then assign the value into a :new.no reference before Oracle actually insert a new record on sample table. Here is the code:

SQL> create or replace trigger tr_bi_sample
2 before insert on sample
3 for each row
4 begin
5 select seq_sampleno.nextval into :new.no from dual;
6 end;
7 /

Trigger created.

Step 4

Now, insert some new records into sample table using the following code:

SQL> insert into sample(description) values('First description');

1 row created.

SQL> insert into sample(description) values('Second description');

1 row created.

SQL> insert into sample(description) values('Third description');

1 row created.

To see the result, you can write the following code:

SQL> col no format 999 heading 'NO'
SQL> col description format A45 heading 'DESCRIPTION'
SQL> select * from sample;

Here is the result of that code:

NO DESCRIPTION
---- ---------------------------------------------
1 First description
2 Second description
3 Third description

I hope this helps you guys....!! :)