触发器可以违反 postgresql 中的外键

Foreign keys in postgresql can be violated by trigger

提问by Michael Clerx

I've created some tables in postgres, added a foreign key from one table to another and set ON DELETE to CASCADE. Strangely enough, I have some fields that appear to be violating this constraint.

我在 postgres 中创建了一些表，将一个外键从一个表添加到另一个表并将 ON DELETE 设置为 CASCADE。奇怪的是，我有一些字段似乎违反了这个限制。

Is this normal behaviour? And if so, is there a way to get the behaviour I want (no violations possible)?

这是正常行为吗？如果是这样，有没有办法获得我想要的行为（不可能有违规行为）？

Edit:

编辑：

I orginaly created the foreign key as part of CREATE TABLE, just using

我 orginaly 创建外键作为 CREATE TABLE 的一部分，只是使用

... REFERENCES product (id) ON UPDATE CASCADE ON DELETE CASCADE

The current code pgAdmin3 gives is

pgAdmin3 给出的当前代码是

ALTER TABLE cultivar
  ADD CONSTRAINT cultivar_id_fkey FOREIGN KEY (id)
      REFERENCES product (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

Edit 2:

编辑2：

To Clarify, I have a sneaking suspicion that the constraints are only checked when updates/inserts happen but are then never looked at again. Unfortunately I don't know enough about postgres to find out if this is true or how fields could end up in the database without those checks being run.

澄清一下，我有一个偷偷摸摸的怀疑，只有在更新/插入发生时才会检查约束，但之后再也不会被查看。不幸的是，我对 postgres 知之甚少，无法确定这是否属实，或者字段如何在没有运行这些检查的情况下最终出现在数据库中。

If this is the case, is there some way to check all the foreign keys and fix those problems?

如果是这种情况，是否有某种方法可以检查所有外键并解决这些问题？

Edit 3:

编辑3：

A constraint violation can be caused by a faulty trigger, see below

错误的触发器可能会导致违反约束，请参见下文

采纳答案by Michael Clerx

Everything I've read so far seems to suggest that constraints are only checked when the data is inserted. (Or when the constraint is created) For example the manual on set constraints.

到目前为止我读过的所有内容似乎都表明只有在插入数据时才会检查约束。（或者当约束被创建时）例如设置约束的手册。

This makes sense and - if the database works properly - should be good enough. I'm still curious how I managed to circumvent this or if I just read the situation wrong and there was never a real constraint violation to begin with.

这是有道理的 - 如果数据库工作正常 - 应该足够好。我仍然很好奇我是如何设法规避这一点的，或者我是否只是错误地理解了情况并且从来没有真正违反约束。

Either way, case closed :-/

无论哪种方式，案例已关闭：-/

------- UPDATE --------

- - - - 更新 - - - -

There was definitely a constraint violation, caused by a faulty trigger. Here's a script to replicate:

肯定存在由错误触发器引起的约束违规。这是一个要复制的脚本：

-- Create master table
CREATE TABLE product
(
  id INT NOT NULL PRIMARY KEY
);

-- Create second table, referencing the first
CREATE TABLE example
(
  id int PRIMARY KEY REFERENCES product (id) ON DELETE CASCADE
);

-- Create a (broken) trigger function
--CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION delete_product()
  RETURNS trigger AS
$BODY$
    BEGIN
      DELETE FROM product WHERE product.id = OLD.id;
      -- This is an error!
      RETURN null;
    END;
$BODY$
  LANGUAGE plpgsql;

-- Add it to the second table
CREATE TRIGGER example_delete
  BEFORE DELETE
  ON example
  FOR EACH ROW
  EXECUTE PROCEDURE delete_product();

-- Now lets add a row
INSERT INTO product (id) VALUES (1);
INSERT INTO example (id) VALUES (1);

-- And now lets delete the row
DELETE FROM example WHERE id = 1;

/*
Now if everything is working, this should return two columns:
(pid,eid)=(1,1). However, it returns only the example id, so
(pid,eid)=(0,1). This means the foreign key constraint on the
example table is violated.
*/
SELECT product.id AS pid, example.id AS eid FROM product FULL JOIN example ON product.id = example.id;

回答by Kuberchaun

I tried to create a simple example that shows foreign key constraint being enforced. With this example I prove I'm not allowed to enter data that violates the fk and I prove that if the fk is not in place during insert, and I enable the fk, the fk constraint throws an error telling me data violates the fk. So I'm not seeing how you have data in the table that violates a fk that is in place. I'm on 9.0, but this should not be different on 8.3. If you can show a working example that proves your issue that might help.

我试图创建一个简单的示例，显示正在执行的外键约束。通过这个例子，我证明我不允许输入违反 fk 的数据，我证明如果在插入期间 fk 不在位，并且我启用了 fk，fk 约束会抛出一个错误，告诉我数据违反了 fk。所以我没有看到表中的数据如何违反现有的 fk。我在 9.0 上，但这在 8.3 上应该没有什么不同。如果您可以展示一个工作示例来证明您的问题可能会有所帮助。

--CREATE TABLES--
CREATE TABLE parent
(
  parent_id integer NOT NULL,
  first_name character varying(50) NOT NULL,
  CONSTRAINT pk_parent PRIMARY KEY (parent_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE parent OWNER TO postgres;

CREATE TABLE child
(
  child_id integer NOT NULL,
  parent_id integer NOT NULL,
  first_name character varying(50) NOT NULL,
  CONSTRAINT pk_child PRIMARY KEY (child_id),
  CONSTRAINT fk1_child FOREIGN KEY (parent_id)
      REFERENCES parent (parent_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE child OWNER TO postgres;
--CREATE TABLES--

--INSERT TEST DATA--
INSERT INTO parent(parent_id,first_name)
SELECT 1,'Daddy'
UNION 
SELECT 2,'Mommy';

INSERT INTO child(child_id,parent_id,first_name)
SELECT 1,1,'Billy'
UNION 
SELECT 2,1,'Jenny'
UNION 
SELECT 3,1,'Kimmy'
UNION 
SELECT 4,2,'Billy'
UNION 
SELECT 5,2,'Jenny'
UNION 
SELECT 6,2,'Kimmy';
--INSERT TEST DATA--

--SHOW THE DATA WE HAVE--
select parent.first_name,
       child.first_name
from parent
inner join child
        on child.parent_id = parent.parent_id
order by parent.first_name, child.first_name asc;
--SHOW THE DATA WE HAVE--

--DELETE PARENT WHO HAS CHILDREN--
BEGIN TRANSACTION;
delete from parent
where parent_id = 1;

--Check to see if any children that were linked to Daddy are still there?
--None there so the cascade delete worked.
select parent.first_name,
       child.first_name
from parent
right outer join child
        on child.parent_id = parent.parent_id
order by parent.first_name, child.first_name asc;
ROLLBACK TRANSACTION;


--TRY ALLOW NO REFERENTIAL DATA IN--
BEGIN TRANSACTION;

--Get rid of fk constraint so we can insert red headed step child
ALTER TABLE child DROP CONSTRAINT fk1_child;

INSERT INTO child(child_id,parent_id,first_name)
SELECT 7,99999,'Red Headed Step Child';

select parent.first_name,
       child.first_name
from parent
right outer join child
        on child.parent_id = parent.parent_id
order by parent.first_name, child.first_name asc;

--Will throw FK check violation because parent 99999 doesn't exist in parent table
ALTER TABLE child
  ADD CONSTRAINT fk1_child FOREIGN KEY (parent_id)
      REFERENCES parent (parent_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

ROLLBACK TRANSACTION;
--TRY ALLOW NO REFERENTIAL DATA IN--

--DROP TABLE parent;
--DROP TABLE child;