Re: [SQL] rule or trigger?
M.D.G. Lange wrote: In order for a "dictionary" system I have created the following system: tbllanguages - ID - name Primary key ( ID ) tbldictionary - wordid - languageid - value Primary key ( wordid, languageid) The idea is to have a word id in several languages, so you only have to look up the word id and give the language you would like to get the message in and you'll be presented the translation. So far so good... only wordid is not unique, making it not suitable to use it in foreign keys... however I'd like a similar idea: tblsystemmessages - ID - wordid - pgsqlerrorcode Primary key ( ID ) "Foreign key" wordid references tbldictionary.wordid It is not possible to create a constraint Foreign key for "wordid". No problem there, but I want to be certain that a given wordid exists in tbldictionary. I'd split the existence of the word from its presence in the language-lookup table. Then you can have a reference to the existence of the "raw word". raw_words - wordid - wordname Of course "wordname" will probably be in one of your target languages anyway, but conceptually doesn't have to be. You can always have triggers on tbldictionary to automatically insert into raw_words if you'd like. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] rule or trigger?
On Tue, 7 Jun 2005, M.D.G. Lange wrote: > It is not possible to create a constraint Foreign key for "wordid". No > problem there, but I want to be certain that a given wordid exists in > tbldictionary. > Would I have to create a "RULE" or a "TRIGGER" to be certain that the > wordid is existing in tbldictionary in whatever language. > > I have the idea that a trigger will not prevent the insertion, or did I > not read well enough? What you can do in an after trigger is test that the value exists in the other table or raise an error (which is basically what the foreign key triggers do). There are some issues with writing your own, the first being that to get full foreign key style semantics you need triggers on the referenced table as well, however if removing or updating the wordid in tbldictionary is uncommon, you can probably avoid it. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] rule or trigger?
In order for a "dictionary" system I have created the following system: tbllanguages - ID - name Primary key ( ID ) tbldictionary - wordid - languageid - value Primary key ( wordid, languageid) The idea is to have a word id in several languages, so you only have to look up the word id and give the language you would like to get the message in and you'll be presented the translation. So far so good... only wordid is not unique, making it not suitable to use it in foreign keys... however I'd like a similar idea: tblsystemmessages - ID - wordid - pgsqlerrorcode Primary key ( ID ) "Foreign key" wordid references tbldictionary.wordid It is not possible to create a constraint Foreign key for "wordid". No problem there, but I want to be certain that a given wordid exists in tbldictionary. Would I have to create a "RULE" or a "TRIGGER" to be certain that the wordid is existing in tbldictionary in whatever language. I have the idea that a trigger will not prevent the insertion, or did I not read well enough? Would the following rule do what I want? CREATE OR REPLACE RULE 'systemmessages_rule' AS ON INSERT TO "public.tblsystemmessages" DO INSTEAD ( IF count ( SELECT DISTINCT tbldictionary.wordid ) > 0 INSERT INTO tblsystemmessages ( ID, wordid, pgsqlerrorcode ) VALUES ( NEW.ID, NEW.wordid, NEW.pgsqlerrorcode ) ELSE NOTHING ); But would this not recursively call this rule? Michiel ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
