pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
must 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>
  2001-04-26 04:28 ` Re: must I create the function check_primary_key ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-04-26 15:58 ` Re: must I create the function check_primary_key ? Peter J. Schoenster <peter@schoenster.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 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>
  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 03:48 must I create the function check_primary_key ? Peter J. Schoenster <borg@errorcode.com>
@ 2001-04-26 15:58 ` Peter J. Schoenster <peter@schoenster.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