sql - 使用 oid 作为主键

Question

是否可以使用 oid 数据类型作为主键？

CREATE TABLE "Test"
(
  id oid NOT NULL DEFAULT nextval('"Test_id_seq"'::regclass),
  "some" text,
  CONSTRAINT "Test_pkey" PRIMARY KEY (id)
)

我想使用 unsigned int 数据类型。

到 DrColossos

Furthermore, you can't use foreign key constraints on OIDs (at least this is what google tells me)

CREATE TABLE "Test"
(
  id oid NOT NULL,
  val text NOT NULL,
  CONSTRAINT "Test_pkey" PRIMARY KEY (id)
);

CREATE TABLE "Test2"
(
  id integer NOT NULL DEFAULT nextval('"Test2_Id_seq"'::regclass),
  "TestId" oid NOT NULL,
  CONSTRAINT "Test2_pkey" PRIMARY KEY (id),
  CONSTRAINT "Test2_TestId_fkey" FOREIGN KEY ("TestId")
      REFERENCES "Test" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
);

INSERT INTO "Test"(id, val) VALUES (1, 'one');
INSERT INTO "Test"(id, val) VALUES (2, 'two');

select * from "Test";

insert into "Test2" ("TestId") values (1);
insert into "Test2" ("TestId") values (2);
insert into "Test2" ("TestId") values (4);

ERROR:  insert or update on table "Test2" violates foreign key constraint "Test2_TestId_fkey"
DETAIL:  Key (TestId)=(4) is not present in table "Test".

select * from "Test2";

Answer 1

只要尝试一下，您就会知道这是否可行。答案是肯定的。 (这很不寻常，但我暂时看不出它会导致问题的原因。)