Re: ALTER TABLE to add Foreign Key Constraint

Поиск
Список
Период
Сортировка
Искать

Re: ALTER TABLE to add Foreign Key Constraint

От:
"Robert B. Easter" <reaster@comptechnews.com>
Дата:
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

ALTER TABLE to add Foreign Key Constraint

От:
Vipin Samtani <vipin@attglobal.net>
Дата:
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?

Vipin
FAQ