On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:On Tue, May 3, 2016 at 1:21 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:Well.. I don't need to add a constraint if I already have a default value, that's right...WrongDavid J.NEW.users_code = NEXTVAL(c1_users_code_seq);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 THENELSEIF NEW.company.id = 2 THENNEW.users_code = NEXTVAL(c2_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?
Sequences have some very nice concurrency properties built into them. Using them is the least problematic solution though within the framework you are proposing they do have some tradeoffs to consider.
I would say that a proposal to create a huge if/else block is not very realistic. Sequences have names and you should be storing, passing around, and using those names. You cannot avoid having thousands of names and objects defined, and I have no clue how efficient sequence name lookup is (probably reasonably so), but at least the rest of the logic should be devoid of any huge conditional blocks like the above. If you think you need such a block typically you want to encode said information into a table instead.
You'll need the table regardless but if you want to avoid creating sequence objects you'd have to write custom functions, similar to (subset of...) those implemented for sequence manipulation, to talk query said table.
So, since sequence us just a record on a table, the only differences is it is system table and not a user table, I would recommend simply using sequences unless and until you can prove they are inadequate to your needs AND can prove that whatever custom implementation you write is better.
The only other reasonable option is change your model and requirements to something less complex.
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?
The purpose of this list is whatever people are willing to make of it - within reason. I share the sentiment that your particular method of education is becoming burdensome to the people who volunteer their time on these lists to answer questions. As the alternative is to simply stop replying to your emails be grateful that someone was at least willing to tell you to try other avenues of education.
David J.
