postgresql - 如何将列更新为外键？

Question

这是我创建的表:

CREATE TABLE enrolled
(
  sid integer NOT NULL,
  ccode character varying(6) NOT NULL,
  CONSTRAINT enrolled_pkey1 PRIMARY KEY (sid, ccode)
);

现在我想更新 ccode 列以声明为来自名为 Class 的表的外键。我该怎么做？

Answer 1

ALTER TABLE ONLY enrolled
    ADD CONSTRAINT enrolled_ccode_fkey FOREIGN KEY (ccode)
        REFERENCES class(ccode)
    ON UPDATE CASCADE ON DELETE RESTRICT;

但是要记住，你必须在 class.ccode 列上有一个唯一的索引:

CREATE UNIQUE INDEX class_ccode_idx ON class USING btree (ccode);