Обсуждение: Foreign key
How do they work? Date: Wed, 6 Oct 1999 15:25:27 +0000 (GMT) Subject: Re: [GENERAL] Foreign Key Message-ID: <Pine.LNX.3.96.991006152248.8877H-100000@rabies.toodarkpark.org> I had a look at the above message which involved the refint contrib code. I thought the equivalent might be: CREATE TABLE employee ( emp_id serial primary key, emp_name varchar(30) NOT NULL ); CREATE TABLE emp_expense ( expense_id serial primary key, emp_id int4 references employee match full on update cascade, descr varchar(100) NOT NULL, ondate date not null ); insert into employee values (2,'Myself'); insert into emp_expense values (1,2,'Test','10-06-1999'); insert into emp_expense values (2,2,'Test #2','10-06-1999'); select * from employee; select * from emp_expense; update employee set emp_id=5; select * from emp_expense; -- hope emp_id magically changes to 5 but obviously it can't be, as if I update employee, there is nothing in table employee to say "take a look at emp_expense and update emp_id over there". There only is something in emp_expense to say "check employee to see that emp_id here is valid". Anyone know of a tutorial/give me a hint? Cheers, Patrick
On Wed, 15 Dec 1999, Patrick Welche wrote: > How do they work? > [SNIP] > Anyone know of a tutorial/give me a hint? refint works by adding two triggers, one to the parent table and the other to the child table. refint does have a bug that stems from saveplan() ( or something similar, i forget the function name ) -- if you update/delete a cascading fk, then attempt to update/delete ANOTHER cascading fk, refint will attempt to reuse the first query plan ( ie: delete from employee where emp_id=2, delete from employee where emp_id=3. refint uses the plan from '.. where emp_id=2', which causes it to fail ). ( note that $PGSQL_SRC_ROOT/contrib/spi/refint.example has detailed documentation. ) create sequence employee_seq; create table employee ( emp_id int4 not null default nextval('employee_seq'), emp_name varchar(30) not null, primary key (emp_id) ); create sequence emp_expense_seq; create table emp_expense ( expense_id int4 not null default nextval('emp_expense_seq'), emp_id int4 not null, descr varchar(100) not null, ondate date not null, primary key (expense_id) ); -- parent trigger create trigger employee_empid_pfk before delete or update on employee for each row execute procedure check_foreign_key( '1', 'cascade', 'emp_id', 'emp_expense', 'emp_id' ); -- child trigger create trigger emp_expense_empid_fk before insert or update on emp_expense for each row execute procedure check_primary_key ('emp_id', 'employee', 'emp_id' ); -- data inserts insert into employee (emp_id,emp_name) values( NEXTVAL('employee_seq'), 'Myself'); insert into emp_expense (expense_id,emp_id,descr,ondate) values( NEXTVAL('emp_expense_seq'), currval('employee_seq'), 'Test', CURRENT_DATE); insert into emp_expense (expense_id,emp_id,descr,ondate) values( NEXTVAL('emp_expense_seq'), currval('employee_seq'), 'Test #2', CURRENT_DATE); -- selects caffeine=> select * from employee; emp_id|emp_name ------+-------- 1|Myself (1 row) caffeine=> select * from emp_expense; expense_id|emp_id|descr | ondate ----------+------+-------+---------- 1| 1|Test |12-16-1999 2| 1|Test #2|12-16-1999 (2 rows) -- updates caffeine=> update employee set emp_id=2; UPDATE 1 caffeine=> select * from employee; emp_id|emp_name ------+-------- 2|Myself (1 row) caffeine=> select * from emp_expense; expense_id|emp_id|descr | ondate ----------+------+-------+---------- 1| 2|Test |12-16-1999 2| 2|Test #2|12-16-1999 (2 rows) ( exit psql session due to previously mentioned bug ) -- deletes caffeine=> delete from employee where emp_id=2; DELETE 1 caffeine=> select * from employee; emp_id|emp_name ------+-------- (0 rows) caffeine=> select * from emp_expense; expense_id|emp_id|descr|ondate ----------+------+-----+------ (0 rows) so it works, besides that one bug. of course, pgsql 7.0 will have full foreign key support ( including REFERENCES syntax ). --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "Tell a man that there are 400 billion stars and he'll believe you. Tell him a bench has wet paint and he has to touch it."
Just tried what I wrote ages ago below, and it now magically does work! (Don't know since when) Thanks Jan and co, Patrick On Wed, Dec 15, 1999 at 10:56:10PM +0000, Patrick Welche wrote: > How do they work? > > Date: Wed, 6 Oct 1999 15:25:27 +0000 (GMT) > Subject: Re: [GENERAL] Foreign Key > Message-ID: <Pine.LNX.3.96.991006152248.8877H-100000@rabies.toodarkpark.org> > > I had a look at the above message which involved the refint contrib code. I > thought the equivalent might be: > > CREATE TABLE employee > ( > emp_id serial primary key, > emp_name varchar(30) NOT NULL > ); > > CREATE TABLE emp_expense > ( > expense_id serial primary key, > emp_id int4 references employee match full on update cascade, > descr varchar(100) NOT NULL, > ondate date not null > ); > > insert into employee values (2,'Myself'); > insert into emp_expense values (1,2,'Test','10-06-1999'); > insert into emp_expense values (2,2,'Test #2','10-06-1999'); > select * from employee; > select * from emp_expense; > update employee set emp_id=5; > select * from emp_expense; -- hope emp_id magically changes to 5 > > but obviously it can't be, as if I update employee, there is nothing in table > employee to say "take a look at emp_expense and update emp_id over there". > There only is something in emp_expense to say "check employee to see that > emp_id here is valid". > > Anyone know of a tutorial/give me a hint? > > Cheers, > > Patrick > > ************ >
Never mind......found it.
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (col_name) REFERENCES table_name(col_name) MATCH FULL