Setting up foreign key with different datatype

Question

If I create two tables and I want to set one column as foreign key to another table column why the hell am I allowed to set foreign key column datatype?

It just doesn't make any sense or am I missing something? Is there any scenario where column with foreign keys has different datatype on purpose?

Little more deeper about my concerns, I tried to use pgadmin to build some simple Postgres DB. I made first table with primary key serial datatype. Then I tried to make foreign key but what datatype? I have seen somewhere serial is bigint unsigned. But this option doesn't even exists in pgadmin. Of course I could use sql but then why am I using gui? So I tried Navicat instead, same problem. I feel like with every choice I do another mistake in my DB design...

EDIT:

Perhaps I asked the question wrong way. I was allowed to do build structure:

CREATE TABLE user
(
  id bigint NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE book
(
  user integer,
  CONSTRAINT dependent_user_fkey FOREIGN KEY (user)
      REFERENCES user (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

I insert some data to table user:

INSERT INTO user(id)
    VALUES (5000000000);

But I can't cast following insert:

INSERT INTO book(user)
    VALUES (5000000000);

with ERROR: integer out of range which is understandable, but obvious design error.

And my question is: Why when we set CONSTRAINT, data types are not being validated. If I'm wrong, answer should contain scenario where it is useful to have different data types.

Answer 1

Actually it does make sense here is why:

In a table, you can in fact set any column as its primary key. So it could be integer, double, string, etc. Even though nowadays, we mostly use either integers or, more recently, strings as primary key in a table.

Since the foreign key is pointing to another table's primary key, this is why you need to specify the foreign key's datatype. And it obviously needs to be the same datatype.

EDIT:

SQL implementations are lax on this case as we can see: they do allow compatible types (INT and BIG INT, Float or DECIMAL and DOUBLE) but at your own risk. Just as we can see in your example, below.

However, SQL norms do specify that both datatypes must be the same. If datatype is character, they must have the same length, otherwise, if it is integer, they must have the same size and must both be signed or both unsigned.

You can see by yourself over here, a chapter from a MySQL book published in 2003.

Hope this answers your question.

Answer 2

To answer your question of why you'd ever want different type for a foreign vs. primary key...here is one scenario:

I'm in a situation where an extremely large postgres table is running out of integer values for its id sequence. Lots of other, equally large tables have a foreign key to that parent table.

We are upsizing the ID from integer to bigint, both in the parent table and all the child tables. This requires a full table rewrite. Due to the size of the tables and our uptime commitments and maintenance window size, we cannot rewrite all these tables in one window. We have about three months before it blows up.

So between maintenance windows, we will have primary keys and foreign keys with the same numeric value, but different size columns. This works just fine in our experience.

Even outside an active migration strategy like this, I could see creating a new child table with a bigint foreign key, with the anticipation that "someday" the parent table will get its primary key upsized from integer to bigint.

I don't know if there is any performance penalty with mismatched column sizes. That question is actually what brought me to this page, as I've been unable to find guidance on it online.

(Tangent: Never create any table with an integer id. Go with bigint, no matter what you think your data will look like in ten years. You're welcome.)