CODEWITHSUNDEEP
postgresqlforeign-keyssequence
Johan
Johan

Reputation: 76557

How to set a foreign key to a sequenced id

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.

Upvotes: 3

Views: 5460

Answers (2)

Agent
Agent

Reputation: 1395

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',
    },

Upvotes: 0

Robin
Robin

Reputation: 1698

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 .

Upvotes: 5

Related Questions