OraFAQ Forum: Forms » create unique primary key

Home » Developer & Programmer » Forms » create unique primary key (Oracle Form Builder 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create unique primary key [message #484196]

Sun, 28 November 2010 23:52

theowais
Messages: 75
Registered: October 2009
Location: Multan

Member

Hi Experts,

A Master fom in a tabular format. Id is a display item. I want to auto generate Id with following query.
select nvl(max(issue_id),0)+1 into :issue_mt.issue_id from issue_mt;

It works:
1)When each record is saved before creating new record.

It doesn't work:
2)If multiple records are entered with out saving each record.

What to do to generate unique Id in both cases?

Report message to a moderator

Re: create unique primary key [message #484199 is a reply to message #484196]

Mon, 29 November 2010 00:32

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Dear,

Please try this trigger at block level.


< PRE-INSERT >

Select nvl(max(issue_id),0)+1 into :issue_mt.issue_id From Issue_mt;

Regards,

Irfan

Report message to a moderator

Re: create unique primary key [message #484201 is a reply to message #484199]

Mon, 29 November 2010 00:44

theowais
Messages: 75
Registered: October 2009
Location: Multan

Member

Thanks Irfan.

Now I want to work with serial number in detail block.

But serial number must be unique for each master record.

Will it work???

Report message to a moderator

Re: create unique primary key [message #484209 is a reply to message #484201]

Mon, 29 November 2010 01:13

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"MAX + 1" is, generally speaking, a bad idea. It will fail (sooner or later) in a multi-user environment. And - as you can see - doesn't work smoothly in a single-user (which is you testing a form) environment either .

For such purposes, you should use SEQUENCES.

Report message to a moderator

Re: create unique primary key [message #484210 is a reply to message #484209]

Mon, 29 November 2010 01:18

theowais
Messages: 75
Registered: October 2009
Location: Multan

Member

You are right Littefoot.

some tips required for serial number in detail block.

Report message to a moderator

Re: create unique primary key [message #484225 is a reply to message #484210]

Mon, 29 November 2010 02:07

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The simplest way is to use :SYSTEM.TRIGGER_RECORD to populate record number. This might be OK if detail records' "serial numbers" don't have to be unique (within the whole detail table), but will be unique per foreign key (i.e. master record).

Report message to a moderator