How to make primary and foreign key of different data types

Question

I have two tables and these both are associated based on primary foreign key relationship , in my one table i have the data type of that column tinyint and in another table i have a data type of int , now when i am going to create a relationship it gives an error , I could not change the data type because for that i have to change in hundreds of places how to make the relationship between two columns of different tables having different data types.

Vinay Singh · Answer

Hi This is not possible having different data type. have a look 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 . for detail check the link http://www.informit.com/articles/article.aspx?p=30875&seqNum=8 http://www.geekinterview.com/talk/7843-different-data-type-primary-foreign-keys.html

Gokul · Answer

Refer these link https://msdn.microsoft.com/en-IN/library/ms179610.aspx

Akshay Phadke · Answer

Refer these link http://stackoverflow.com/questions/32267778/setting-up-foreign-key-with-different-datatype