Обсуждение: Constraint Question
Hi Folks, I'm not perfekt in SQL so I'm not really sure, how this can be done. I have a master table stud containing (amongs others) two rows called 'sid' and 'status'. Now I have different tables using constraints to reference this table. For example I have one table called phon with the rows 'phonid', 'sid' and 'status'. 'sid' is currently the Foreign Key and the Entry in Phone is updated oder deleted, when the sid in the master table is. So far so good. The 'status'-row can contain either 1 or -1 and -4. And now I would like to implement the action that if the the status field in the master table for a special sid is changed, all status-fields in phon for entries which referencing this sid are also changed. Can this be done somehow with a foreign key? If it can, how? Else I think it could be done about a trigger that is set in the mastertable for setup. The problem is, the Trigger must be updated everytime a new table is added, that references the master table. Or is it possible to write a Trigger for the phon-table that checks the status field from stud everytime it is updated, so I can implement additional functionality (somewhat like ' if (phon.status=1 or phon.status=-1) and (stud.status is updated) then phon.status=stud.status ' )? Thanks and *greets* Kai...
On Thu, Mar 16, 2006 at 18:11:33 +0100, Kai Hessing <kai.hessing@hobsons.de> wrote: > > The 'status'-row can contain either 1 or -1 and -4. And now I would like > to implement the action that if the the status field in the master table > for a special sid is changed, all status-fields in phon for entries > which referencing this sid are also changed. > > Can this be done somehow with a foreign key? If it can, how? This seems kind of odd. If the values are supposed to be the same, why are you including them in multiple tables? If there aren't supposed to always be the same, you won't be able to use foreign keys to sometimes make them the same.
Bruno Wolff III wrote: > On Thu, Mar 16, 2006 at 18:11:33 +0100, > Kai Hessing <kai.hessing@hobsons.de> wrote: >> >> The 'status'-row can contain either 1 or -1 and -4. And now I would like >> to implement the action that if the the status field in the master table >> for a special sid is changed, all status-fields in phon for entries >> which referencing this sid are also changed. >> >> Can this be done somehow with a foreign key? If it can, how? > > This seems kind of odd. If the values are supposed to be the same, why are > you including them in multiple tables? If there aren't supposed to always > be the same, you won't be able to use foreign keys to sometimes make them > the same. OK, it is really a little bit odd. I try to explain what should be done. I have 1:n connection between stud and phon (One student does have different phonenumbers, fax, eMails, etc...). A positive status means that the entry is active and a negative status means that the entry is inactive. So I have the possibility to set individual entries in phon to inactive while the corresponding entry in stud is still active. But if the entry in stud is set to inactive all corresponding entries in phon should also be set to inactive. I think I will put a Trigger on stud.status which updates the status of all connected tables. The only thing is, that the trigger must be updated every time I add a new table. So I like the idea of foreign keys which are doing it the reverse way and are set on the table which should be updated, and this table stays to be some sort of an independet module without the need of modifiing another table to update this one. But I think the trigger would be the only usable possibility. So I have to get a little bit used to triggers and their functions. Is it right, that the trigger event is fired each time an UPDATE on the table is executed, or can it be configured only to be fired if an UPDATE on the status-row is performed? I think for performance reasons it would definitly the better way. > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq Yeah, didn't found anything about it.
On Fri, Mar 17, 2006 at 10:33:18 +0100, Kai Hessing <kai.hessing@hobsons.de> wrote: > > OK, it is really a little bit odd. I try to explain what should be done. > I have 1:n connection between stud and phon (One student does have > different phonenumbers, fax, eMails, etc...). A positive status means > that the entry is active and a negative status means that the entry is > inactive. So I have the possibility to set individual entries in phon to > inactive while the corresponding entry in stud is still active. But if > the entry in stud is set to inactive all corresponding entries in phon > should also be set to inactive. > > I think I will put a Trigger on stud.status which updates the status of > all connected tables. The only thing is, that the trigger must be > updated every time I add a new table. So I like the idea of foreign keys > which are doing it the reverse way and are set on the table which should > be updated, and this table stays to be some sort of an independet module > without the need of modifiing another table to update this one. But I > think the trigger would be the only usable possibility. So I have to get > a little bit used to triggers and their functions. Foreign keys won't work for this. You are going to need to have triggers on both the stud and phon tables. > Is it right, that the trigger event is fired each time an UPDATE on the > table is executed, or can it be configured only to be fired if an UPDATE > on the status-row is performed? I think for performance reasons it would > definitly the better way. Currently you can't trigger on specific columns. However, the trigger could check the status column to see if it had changed and do less work if it hasn't.