Re: ALTER TABLE to add Foreign Key Constraint
От | Robert B. Easter |
---|---|
Тема | Re: ALTER TABLE to add Foreign Key Constraint |
Дата | |
Msg-id | 00061723004000.18758@comptechnews обсуждение исходный текст |
Ответ на | ALTER TABLE to add Foreign Key Constraint (Vipin Samtani <vipin@attglobal.net>) |
Список | pgsql-general |
On Sat, 17 Jun 2000, Vipin Samtani wrote: > I am trying to designate foreign keys after tables "distributors" and > "addresses" have already been created with primary keys "distributor" > and "address" respectively. I am attempting to use ALTER TABLE to > accomplish this. I found this statement in the documentation > > ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) > REFERENCES addresses(address) MATCH FULL > > > When I run it, I get this error: > > ERROR: parser: parse error at or near "constraint" > > What is wrong with the statement? Consider this example: -- Load the PGSQL procedural language -- This could also be done with the createlang script/program. -- See man createlang. CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE test1 (id INTEGER PRIMARY KEY); CREATE TABLE test2 (id INTEGER PRIMARY KEY); ALTER TABLE test1 ADD CONSTRAINT test1_id_fk FOREIGN KEY (id) REFERENCES test2 ON UPDATE CASCADE ON DELETE CASCADE -- can delete test2 INITIALLY DEFERRED; ALTER TABLE test2 ADD CONSTRAINT test1_id_fk FOREIGN KEY (id) REFERENCES test1 ON UPDATE CASCADE ON DELETE RESTRICT -- disallows delete test1 INITIALLY DEFERRED; CREATE SEQUENCE test_id_seq; CREATE FUNCTION new_tests() RETURNS INTEGER AS ' DECLARE new_seq INTEGER; BEGIN new_seq := nextval(''test_id_seq''); INSERT INTO test1 VALUES (new_seq); INSERT INTO test2 VALUES (new_seq); RETURN new_seq; END; ' LANGUAGE 'plpgsql'; -- implicit BEGIN; SELECT new_tests(); -- implicit COMMIT; SELECT new_tests(); SELECT new_tests(); SELECT * FROM test1; SELECT * FROM test2; DELETE FROM test1 WHERE id = 1; -- this will fail DELETE FROM test2 WHERE id = 1; -- this will succeed and cacade SELECT * FROM test1; SELECT * FROM test2; -- Robert B. Easter
В списке pgsql-general по дате отправления: