Thread: must I create the function check_primary_key ?

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,typevarchar (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

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. 

> 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