sql - 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.