Setting Up Foreign Key with Different Datatype

Setting up foreign key with different datatype

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.

create foreign key in different data type

I absolutely agree with those who left comments: the best answer is to change the type of OrderDetail.OrderID to match OrderHeader.ID.

Doing anything else is a very bad idea.

But, assuming that you can't do that for some reason, you can create a foreign key, ... kind of .. sort of.

You can create a computed field that casts the one datatype to the other, and set up a foreign key on that field. Note, in order to do this, the computed field must be persisted.

create table OrderHeader ( ID char(12) primary key )

create table OrderDetail ( OrderID char(10),
                           OrderID12 as cast ( OrderID as char(12) )
                           persisted
                           foreign key references OrderHeader(ID)
                           )

insert into OrderHeader ( ID ) values ( 'MOH333' )

insert into OrderDetail ( OrderID ) values ( 'MOH333' )

-- this one fails
insert into OrderDetail ( OrderID ) values ( '777PIR' )

Entity Framework Core: FK with Different DataType than PK

In EF Core 2.1 you can use value conversions, i.e.

entityBuilder.Property(c => c.Id).HasConversion<string>(); 

where the generic string is the database type and the destination is the model type. You can read further on https://learn.microsoft.com/es-es/ef/core/modeling/value-conversions

Is there any way to reference column with different datatype?

If schema2 is on Oracle 11g and above, using virtual column may solve your problem, but that will change the structure of your table which you are trying to avoid. If you can manage it, here is how it can be done

SQL> CREATE TABLE code_tbl
  2  ( code        CHAR(6) PRIMARY KEY,
  3    description CHAR(30)
  4  );
Table created

SQL> 
SQL> CREATE TABLE record_tbl
  2  ( rec_id    VARCHAR2(10) PRIMARY KEY,
  3    curr_code VARCHAR2(6),
  4    remarks   VARCHAR2(30)
  5  );
Table created

SQL> INSERT INTO code_tbl(code, description)  VALUES ('ABC', 'Test Data');
1 row inserted

SQL> INSERT INTO record_tbl(rec_id, curr_code, remarks) VALUES ('1', 'ABC', 'Test Row');
1 row inserted

SQL> SELECT * FROM record_tbl;
REC_ID     CURR_CODE REMARKS
---------- --------- ------------------------------
1          ABC       Test Row

SQL> SELECT * FROM code_tbl;
CODE   DESCRIPTION
------ ------------------------------
ABC    Test Data

SQL> ALTER TABLE record_tbl ADD curr_code_v CHAR(6) AS (trim(curr_code));
Table altered

SQL> SELECT * FROM record_tbl;
REC_ID     CURR_CODE REMARKS                        CURR_CODE_V
---------- --------- ------------------------------ -----------
1          ABC       Test Row                       ABC

SQL> 
SQL> ALTER TABLE record_tbl
  2  ADD CONSTRAINT record_code_fk
  3  FOREIGN KEY (curr_code_v)
  4  REFERENCES code_tbl (CODE);
Table altered

SQL> INSERT INTO record_tbl(rec_id, curr_code, remarks)  VALUES ('2', 'ABC', 'Test Row 2');
1 row inserted

SQL> INSERT INTO record_tbl(rec_id, curr_code, remarks)  VALUES ('3', 'XYZ', 'Test Row 2');
INSERT INTO record_tbl(rec_id, curr_code, remarks)  VALUES ('3', 'XYZ', 'Test Row 2')
ORA-02291: integrity constraint (USER_X.RECORD_CODE_FK) violated - parent key not found

Here are the words from Tom Kyte regarding virtual columns:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:676611400346196844

How to modify data type of a column that is foreign key or primary key?

Drop the foreign key, change both tables and add the foreign key. It is btw good idea to name your constraints explicitly. Now you will have to look up what the constraint name is in the catalog:

select CONSTRAINT_NAME from information_schema.REFERENTIAL_CONSTRAINTS
where TABLE_NAME = 'prerequisite'
  and REFERENCED_TABLE_NAME = 'course'

alter table prerequisite drop constraint ...;

alter table course change course_number course_number varchar(20);
alter table prerequisite change course_number course_number varchar(20);

alter table prerequisite add constraint <name>
    foreign key (course_number)
    references course (course_number)
        <actions>;

Trying to set Foreign Key as string datatype

try with $table->string('user_id')->unique();
it will help you and If you would like the relationship to use a value other than id, you may pass a third argument to the hasOne method specifying your custom key:
Like this:

return $this->hasOne('App\Phone', 'foreign_key', 'local_key');

Foreign key rejected despite columns having different data types

Although the foreign and primary key columns involved here are the same type, you are trying to reference clothing.main, which is not a unique or primary key column. From the MariaDB documentation:

The referenced columns must be a PRIMARY KEY or a UNIQUE index.

Note that this differs from InnoDB on MySQL, where a foreign key column can in fact reference a non unique column in another table.

One way to remedy this error would be to make clothing.main a unique column:

ALTER TABLE clothing ADD UNIQUE (main);

Note that doing this might only make logical sense if the values in main are already unique. If not, then perhaps you would have to revisit your data model.

Why should my FK column and the referenced column have the same type?

Big int can be as large as 9,223,372,036,854,775,807, while int only goes to 2,147,483,647. Therefore, if your data grows large enough to surpass 2,147,483,647 like 2,147,483,648 for example, you would no longer be able to reference your post in your comments.post_id column.

---

Related Topics