Thread: Limit A Table To 1 Row
Hello! Corporate table, t1, should contain exact 1 row. I strongly believe that there are cleaner versions than the following combination of trigger and rule. Thank you in advance for any improved version! CNLIOU -------- CREATE TABLE t1 (CorpId TEXT,CorpName TEXT); CREATE FUNCTION f1() RETURNS OPAQUE AS ' DECLARE rc SMALLINT; BEGIN SELECT COUNT(*) INTO rc FROM t1; IF rc > 0 THEN RETURN NULL; END IF; RETURN NEW; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE f1(); CREATE RULE rule1 AS ON DELETE TO t1 DO INSTEAD NOTHING; -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com
On Wed, Aug 07, 2002 at 03:29:50 +0000, cnliou@eurosport.com wrote: > Hello! > > Corporate table, t1, should contain exact 1 row. > I strongly believe that there are cleaner versions > than the following combination of trigger and rule. > > Thank you in advance for any improved version! Another way to keep someone from inserting a second row is to have a primary key and a constraint fixing the primary key to a fixed value.
Cnliou, > Corporate table, t1, should contain exact 1 row. > I strongly believe that there are cleaner versions > than the following combination of trigger and rule. Actually, your method looks quite effective and relatively simple. However, you may want to reconsider this method of storing values in the database. What I do is create a table called global_admin: CREATE TABLE global_admin (value_name VARCHAR(30) NOT NULL PRIMARY KEY,value_format VARCHAR(30) NOT NULL,admin_value TEXTNULL ); And then I can store all of my random, persistent setting data in one table: value_name value_format admin_value 'Coporation' 'Text' 'Lugini Corp., Inc.' 'Home Town' 'Text' 'Singapore' 'Timeout Interval' 'Interval' '30 minutes' 'Max Reports' 'Integer' '5' etc. -Josh Berkus
On Wednesday 07 Aug 2002 4:52 am, Josh Berkus wrote: > However, you may want to reconsider this method of storing values in > the database. What I do is create a table called global_admin: > > CREATE TABLE global_admin ( > value_name VARCHAR(30) NOT NULL PRIMARY KEY, > value_format VARCHAR(30) NOT NULL, > admin_value TEXT NULL > ); I do as Josh does (which has got to put some doubts in Josh's mind) except I add value_section and value_comment fields to make it easier for someone to edit where necessary. - Richard Huxton