On 05/03/2016 02:27 PM, [email protected] wrote:
On 4 May 2016 at 01:18, Melvin Davidson <[email protected]
<mailto:[email protected]>> wrote:
On Tue, May 3, 2016 at 1:21 AM, David G. Johnston
<[email protected] <mailto:[email protected]>> wrote:
Well.. I don't need to add a constraint if I already have a
default value, that's right...
Wrong
David J.
What you need is a TRIGGER function & TRIGGER that will select and
assign the next users_code based on company_id.
I'm not going to write the whole thing for you, but here is part of
the trigger function logic.
eg: IF NEW.company_id = 1 THEN
NEW.users_code = NEXTVAL(c1_users_code_seq);
ELSEIF NEW.company.id <http://NEW.company.id> = 2 THEN
NEW.users_code = NEXTVAL(c2_users_code_seq);
ELSEIF NEW.company.id <http://NEW.company.id> = 3 THEN
NEW.users_code = NEXTVAL(c3_users_code_seq);
...
...
ELSE
< something bad happened because NEW.company_id was
not valid ?
END IF;
Do I have to have one sequence peer company_id ? There will be
thousands.. isn't there a better way to do that?
A sequence just keeps on incrementing. If you want a gapless sequence
for each company that each increment independently of each other then
yes you will need a separate sequence for each.
For an alternate strategy see this related thread:
http://www.postgresql.org/message-id/[email protected]
in particular this link from the thread:
http://www.postgresql.org/message-id/[email protected]
Seriously, get yourself the books I have recommended and study them
BEFORE you continue attempting to design your database.
You need a firm understanding of logical design & flow, otherwise
you will be wasting your time.
That's what I'm doing.. Studying.. asking for some help to get a better
understand.... isn't this the purpose of this mail list?
--
Adrian Klaver
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
