pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmust I create the function check_primary_key ?
3+ messages / 3 participants
[nested] [flat]
* must I create the function check_primary_key ?
@ 2001-04-26 03:48 Peter J. Schoenster <borg@errorcode.com>
0 siblings, 2 replies; 3+ messages in thread
From: Peter J. Schoenster @ 2001-04-26 03:48 UTC (permalink / raw)
To: pgsql-sql
Hi,
Thanks to many who clued me on on using "arrow keys" to
traverse history. I just installed readline and 7.1 .. seems that
mysql has its own readline.
I want to use this referential integrity etc. that I've never used in
mysql ... so I tried many things and looked here and there ... My
example below produces this error:
> CreateTrigger: function check_primary_key() does not exist
Here is what I was trying to create:
DROP TABLE company_profile;
CREATE TABLE company_profile (
employer_id int4,
leadership text,
company_description text,
key_financial_statictics varchar (255),
company_name varchar (255),
url varchar (255),
sec_fillings_url varchar (255),
mission text,
employees int4,
type varchar (50),
growth_rate varchar (50),
culture text,
year int4,
PRIMARY KEY (employer_id)
);
CREATE TRIGGER employer_id_exists
BEFORE INSERT OR UPDATE ON company_profile FOR EACH
ROW
EXECUTE PROCEDURE check_primary_key('employer_id',
'employer_profile', 'employer_id');
__end example
I just copied the above TRIGGER .... but it doesn't work for me.
Thanks,
Peter
All idioms must be learned.
Good idioms only need to be learned once.
--Alan Cooper
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: must I create the function check_primary_key ?
@ 2001-04-26 04:28 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Peter J. Schoenster <borg@errorcode.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2001-04-26 04:28 UTC (permalink / raw)
To: Peter J. Schoenster <borg@errorcode.com>; +Cc: pgsql-sql
On Wed, 25 Apr 2001, Peter J. Schoenster wrote:
> I want to use this referential integrity etc. that I've never used in
> mysql ... so I tried many things and looked here and there ... My
> example below produces this error:
>
> > CreateTrigger: function check_primary_key() does not exist
>
> Here is what I was trying to create:
>
> DROP TABLE company_profile;
>
> CREATE TABLE company_profile (
> employer_id int4,
> leadership text,
> company_description text,
> key_financial_statictics varchar (255),
> company_name varchar (255),
> url varchar (255),
> sec_fillings_url varchar (255),
> mission text,
> employees int4,
> type varchar (50),
> growth_rate varchar (50),
> culture text,
> year int4,
> PRIMARY KEY (employer_id)
> );
>
> CREATE TRIGGER employer_id_exists
> BEFORE INSERT OR UPDATE ON company_profile FOR EACH
> ROW
> EXECUTE PROCEDURE check_primary_key('employer_id',
> 'employer_profile', 'employer_id');
You probably just want to use a REFERENCES constraint
employer_id int4 REFERENCES employer_profile(employer_id)
You'll need to have a primary key or unique constraint
on employer_profile(employer_id). The references constraint
will also prevent you from deleting a employer_id
row that is being referenced.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: must I create the function check_primary_key ?
@ 2001-04-26 15:58 Peter J. Schoenster <peter@schoenster.com>
parent: Peter J. Schoenster <borg@errorcode.com>
1 sibling, 0 replies; 3+ messages in thread
From: Peter J. Schoenster @ 2001-04-26 15:58 UTC (permalink / raw)
To: pgsql-sql
> On Wed, 25 Apr 2001, Peter J. Schoenster wrote:
>
> > I want to use this referential integrity etc. that I've never used
..snip..
> > CREATE TRIGGER employer_id_exists
> > BEFORE INSERT OR UPDATE ON company_profile FOR EACH
> > ROW
> > EXECUTE PROCEDURE check_primary_key('employer_id',
> > 'employer_profile', 'employer_id');
Stephan Szabo <sszabo@megazone23.bigpanda.com>
enlightened me as such:
> You probably just want to use a REFERENCES constraint
> employer_id int4 REFERENCES employer_profile(employer_id)
>
> You'll need to have a primary key or unique constraint
> on employer_profile(employer_id). The references constraint
> will also prevent you from deleting a employer_id
> row that is being referenced.
Banging my head on the table ... yes ... I never set the constraint
on employer_id in employer_profile. I should have been more
patient in reading the error message returned by psql.
Thanks for the help. Your suggestion worked as expected.
Peter
All idioms must be learned.
Good idioms only need to be learned once.
--Alan Cooper
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-04-26 15:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-26 03:48 must I create the function check_primary_key ? Peter J. Schoenster <borg@errorcode.com>
2001-04-26 04:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-04-26 15:58 ` Peter J. Schoenster <peter@schoenster.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox