Обсуждение: 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
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 (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
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.
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
Sorry, I missed one table. The users_facility_levels table is used to link the three tables listed below to give the lever per user/facility. On Wednesday 22 February 2012 11:18:23 Oliveiros d'Azevedo Cristina wrote: > 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 (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk