Hi,
A quick question about Inheritance here.
If a table has FOREIGN or PRIMARY KEYs in it, and then another table which
inherits this table is created, the inherited table will not have the KEY
constraints. Is this correct?
In the documentation it is does not explicitly say whether constraints are
inherited or not so I just wanted to check (it might also be worth adding it
to the docs for dumb people like me :-)
I've included an example below in case my explanation is not clear.
This is actually the way I want it to work for keeping a history, but I'm
just checking that this is the correct behaviour and it's not going to be
"fixed" in a future version (which would mean I would have to rewrite the
scripts).
Thanks in advance,
---[ Neil Burrows ]-----------------------------------------------------
E-mail: neil.burrows@ssh.com | SSH Communication Security Corp.
Web : http://www.ssh.com/ | Keeping the Internet secure
-----< Any views expressed are not necessarily those of my employer >-----
CREATE TABLE ref(
id INT4 PRIMARY KEY,
val VARCHAR(128)
);
CREATE TABLE parent(
id INT4 PRIMARY KEY,
ref INT4,
val VARCHAR(128),
FOREIGN KEY (ref) REFERENCES ref(id)
);
CREATE TABLE parent_history(
) inherits(parent);
CREATE FUNCTION backup_parent () RETURNS OPAQUE as '
BEGIN
INSERT INTO parent_history SELECT * FROM parent WHERE id = OLD.id;
return OLD;
END; '
LANGUAGE 'plpgsql';
CREATE TRIGGER parent_trigger
BEFORE DELETE OR UPDATE ON parent FOR EACH ROW
EXECUTE PROCEDURE backup_parent ();
INSERT INTO ref VALUES (1, 'test ref');
INSERT INTO parent VALUES (1, 1, 'test parent');
-- This should fail to to integrety check
DELETE FROM ref;
DELETE FROM parent;
-- This will no longer fail
DELETE FROM ref;
-- Show value in history
SELECT * FROM parent_history;