[SQL] Another constant in foreign key problem.
I have three tables, users - all users of my web site facilities - facilities available on my web site facility_levels - access levels per user/facility. One of my facilities is a document library (f_id = 22) For this facility I have the levels select * from facility_levels where fl_f_id=22 order by fl_level; fl_f_id | fl_level | fl_desc -+--+-- 22 |1 | Read Only 22 |2 | Add Versions 22 |3 | Amend (3 rows) This sets the global access level for the Document Library per user. I now want to add authentication control on a document or folder level. For this I need to create a table library_document_user_level u_id - user id ld_id - library document id fl_level - level The foreign key constraint on fl_level needs to check facility_levels for fl_f_id = 22 as well as fl_level existing. I've googled this but can't find a suitable solution. Can anyone help please. -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Another constant in foreign key problem.
Hi, Gary, I'm answering by editing your e-mail __ I have three tables, users - all users of my web site facilities - facilities available on my web site facility_levels - access levels per user/facility. One of my facilities is a document library (f_id = 22) For this facility I have the levels select * from facility_levels where fl_f_id=22 order by fl_level; fl_f_id | fl_level | fl_desc -+--+-- 22 |1 | Read Only 22 |2 | Add Versions 22 |3 | Amend (3 rows) This sets the global access level for the Document Library per user. * It is not clear for me how this sets the global access level per user. Shouldnt the facility_levels table have a u_id field, foreign key from users table? And thus becoming an associative table between users and facilities? I now want to add authentication control on a document or folder level. For this I need to create a table library_document_user_level u_id - user id ld_id - library document id fl_level - level The foreign key constraint on fl_level needs to check facility_levels for fl_f_id = 22 as well as fl_level existing. * I may not be fully understanding your problem, but library_document_user_level shouldn't have a field named fl_f_id, to identify which facility the document/folder belongs to? Had it such a field, you could do something like FOREIGN_KEY (fl_f_id,fl_level) REFERENCES facility_levels (fl_f_id, fl_level) Just my two cents Best, Oliveiros I've googled this but can't find a suitable solution. Can anyone help please. -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Another constant in foreign key problem.
Hi Gary In short you can't have a foreign key here because foreign keys are just that: a *key* from another table. fl_level is not a key, it is not unique and requires fl_f_id to be unique. If you want a foreign key between these two tables then you must add the facility id to the document library and use a composite key (fl_f_id , fl_level) as the foreign key. This may be advantageous as the same structure could be re-used across other facilities (even though it would not be applicable to all). There are a couple of alternatives to this. One is to add a separate column to facility_levels. The sole purpose of this column would be to proved a unique key on every row in the table. Foreign keys (such as on library_document_user_level) would reference this and not fl_level. There would be no constraint here to prevent a row in library_document_user_level from referencing the wrong facility. Another possible alternative which is very PostgreSQL specific is to use inheritance. Create a child table storing only facility_levels for fl_f_id 22. The foreign key would reference the child table and not facility_levels. Everything in the child table would also exist in facility_levels. As this is so specific to PostgreSQL and is not hugely common as a technique, read this as a *possible* solution not a recommended one. Hope this helps On 22/02/2012 10:35, Gary Stainburn wrote: > I have three tables, > > users - all users of my web site > facilities - facilities available on my web site > facility_levels - access levels per user/facility. > > One of my facilities is a document library (f_id = 22) > For this facility I have the levels > > select * from facility_levels where fl_f_id=22 order by fl_level; > fl_f_id | fl_level | fl_desc > -+--+-- > 22 |1 | Read Only > 22 |2 | Add Versions > 22 |3 | Amend > (3 rows) > > > This sets the global access level for the Document Library per user. > > I now want to add authentication control on a document or folder level. For > this I need to create a table library_document_user_level > > u_id - user id > ld_id - library document id > fl_level - level > > The foreign key constraint on fl_level needs to check facility_levels for > fl_f_id = 22 as well as fl_level existing. > > I've googled this but can't find a suitable solution. Can anyone help please. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Another constant in foreign key problem.
Thank you to you both. I was thinking that I wasn't going to be able to do this. As the use of these tables is purely to control the web interface to this database I have decided control this via the application rather than within the database. It will require less coding and is simpler. On Wednesday 22 February 2012 12:04:41 Philip Couling wrote: > Hi Gary > > In short you can't have a foreign key here because foreign keys are just > that: a *key* from another table. fl_level is not a key, it is not > unique and requires fl_f_id to be unique. > > If you want a foreign key between these two tables then you must add the > facility id to the document library and use a composite key (fl_f_id , > fl_level) as the foreign key. This may be advantageous as the same > structure could be re-used across other facilities (even though it would > not be applicable to all). > > > > There are a couple of alternatives to this. One is to add a separate > column to facility_levels. The sole purpose of this column would be to > proved a unique key on every row in the table. Foreign keys (such as on > library_document_user_level) would reference this and not fl_level. > There would be no constraint here to prevent a row in > library_document_user_level from referencing the wrong facility. > > > Another possible alternative which is very PostgreSQL specific is to use > inheritance. Create a child table storing only facility_levels for > fl_f_id 22. The foreign key would reference the child table and not > facility_levels. Everything in the child table would also exist in > facility_levels. As this is so specific to PostgreSQL and is not hugely > common as a technique, read this as a *possible* solution not a > recommended one. > > Hope this helps > -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on insert rule with default value
2012-02-21_15:51:30-0500 Ron Peterson : > My rule below does not insert the the same uuid value into the test_log > table as is created in the test table when I insert a new value. I know > I've worked through this before, but I'm not remembering why this is. > What's a right way to do this? Obviously I can use a trigger function. I'm mostly wondering if there are any tricks to accomplishing this with rules (I like the simple syntax). I suspect the problem here is that 'new' on insert refers to the function used to calculate the new value, /not/ the new value that is actually inserted into the table. There are probably reasons for that; but it would seem nicer to refer to the actual new table value rather than the expression used to calculate it. My 2c. -Ron- > create table test ( > anid > uuid > not null > default encode( gen_random_bytes( 16 ), 'hex' )::uuid > primary key, > value > text > ); > > create table test_log ( > anid > uuid, > value > text, > op > text, > attime > timestamp with time zone > ); > > create rule test_rule_a as > on insert to test do ( > insert into test_log ( anid, value, op, attime ) > values ( new.anid, new.value, 'insert', now() ) > ); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on insert rule with default value
On Wednesday, February 22, 2012 5:52:39 am Ron Peterson wrote: > 2012-02-21_15:51:30-0500 Ron Peterson : > > My rule below does not insert the the same uuid value into the test_log > > table as is created in the test table when I insert a new value. I know > > I've worked through this before, but I'm not remembering why this is. > > What's a right way to do this? > > Obviously I can use a trigger function. I'm mostly wondering if there > are any tricks to accomplishing this with rules (I like the simple > syntax). I suspect the problem here is that 'new' on insert refers to > the function used to calculate the new value, /not/ the new value that > is actually inserted into the table. There are probably reasons for > that; but it would seem nicer to refer to the actual new table value > rather than the expression used to calculate it. My 2c. The simplest explanation I could find is here: http://www.postgresql.org/docs/9.0/interactive/querytree.html "the target list... For INSERT commands, the target list describes the new rows that should go into the result relation. It consists of the expressions in the VALUES clause or the ones from the SELECT clause in INSERT ... SELECT. The first step of the rewrite process adds target list entries for any columns that were not assigned to by the original command but have defaults. Any remaining columns (with neither a given value nor a default) will be filled in by the planner with a constant null expression. " If you want all the gory details read through section 37, in particular 37.3 :) The above is why I use triggers now. It is a lot easier to follow the logic in a trigger than in a rule. > > -Ron- -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Concurrent Reindex on Primary Key for large table
Hi I had another question, what about when the primary key is a foreign key in another table? Is the only option to drop the FK and recreate it after the primary key has been created with the new index? Thanks! RV -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-Reindex-on-Primary-Key-for-large-table-tp5467243p5506261.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
