如何为有序 id 设置外键

发布于 2024-12-11 09:29:38 字数 984 浏览 0 评论 0原文

我有 journal 的表定义

CREATE TABLE "public"."journal" (
"id" "public"."inc_journal" NOT NULL,
"short_desc" varchar(20),
"description" varchar(1000),
"default_gl_account_id" int8,
"company_id" int2,
"creator_user_id" int4,
"created_date" timestamp(6),
"type" "public"."journal_type",
CONSTRAINT "journal_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;

inc_journal 的 def 是这样的序列：

CREATE SEQUENCE "public"."inc_journal"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 4294967295
 START 1
 CACHE 1;

我希望像这样为其设置外键：

ALTER TABLE "public"."entry"
ADD FOREIGN KEY ("journal_id") REFERENCES "public"."journal" ("id");

但是，当我这样做时，我收到错误：

[Err]错误：无法实现外键约束“entry_journal_id_fkey”
详细信息：键列“journal_id”和“id”的类型不兼容：整数和 inc_journal。

我该如何消除这个错误？
我需要设置 journal_id 来输入 inc_journal 吗？我仍然想在字段中插入 null ，所以这似乎不是正确的选择。

原文

I have the following table definition for journal

CREATE TABLE "public"."journal" (
"id" "public"."inc_journal" NOT NULL,
"short_desc" varchar(20),
"description" varchar(1000),
"default_gl_account_id" int8,
"company_id" int2,
"creator_user_id" int4,
"created_date" timestamp(6),
"type" "public"."journal_type",
CONSTRAINT "journal_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;

The def for inc_journal is a sequence like so:

CREATE SEQUENCE "public"."inc_journal"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 4294967295
 START 1
 CACHE 1;

And I wish to set a foreign key to it like so:

ALTER TABLE "public"."entry"
ADD FOREIGN KEY ("journal_id") REFERENCES "public"."journal" ("id");

However when I do this I get an error:

[Err] ERROR: foreign key constraint "entry_journal_id_fkey" cannot be implemented
DETAIL: Key columns "journal_id" and "id" are of incompatible types: integer and inc_journal.

How do I get rid of this error?
Do I need to set journal_id to type inc_journal? I'd like to still insert null into the field, so this does not seem the right option.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

攒眉千度 2024-12-18 09:29:38

尝试为您的 PKey 使用字段类型 serial，或者

id integer NOT NULL DEFAULT nextval('inc_journal')

这将为您的 PKey 创建/使用序列。然后，您可以按 FKey 键入任何您想要的整数字段。另请参阅 http://www.postgresql.org/docs /current/static/datatype-numeric.html#DATATYPE-SERIAL 。

Try using field type serial for you PKey, or

id integer NOT NULL DEFAULT nextval('inc_journal')

This will create/use a sequence for your PKey. You can then FKey any integer fields you want. See also http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL .

回复收藏 0 原文

北凤男飞 2024-12-18 09:29:38

如果您使用 Sequerize ORM 并收到错误，请尝试以下操作。

我犯了一个错误，产品表的外键字段与用户表的主键字段具有不同的类型桌子。当我匹配类型时，它起作用了！

示例

// user 
    id: {
      type: Sequelize.UUID,     // Foreign key type should match with PK
      allowNull: false,
      primaryKey: true,
      defaultValue: Sequelize.UUIDV4,
   }

// product
    userId: {
      type: Sequelize.UUID,  // FK type should be the same as PK type
      allowNull: false,
      references: {
      model: 'Users',
      key: 'id',
    },

If you are using Sequerize ORM and get the error try the following.

I made a mistake where Foreign key field of a product table had different type as Primary key field of a user table. When I match the type, it works!

Example

// user 
    id: {
      type: Sequelize.UUID,     // Foreign key type should match with PK
      allowNull: false,
      primaryKey: true,
      defaultValue: Sequelize.UUIDV4,
   }

// product
    userId: {
      type: Sequelize.UUID,  // FK type should be the same as PK type
      allowNull: false,
      references: {
      model: 'Users',
      key: 'id',
    },

回复收藏 0 原文

~没有更多了~