Not Making Relationship between Primary and Foreign key in S

Question

I have made a database and it is mature now  unfortunately I have mismatch column data types for my primary and foreign key relationship  Just like in my First table I have RecordsID as bigint and in my second table Where this REcordID is foreign key I have tinyint as datatype my record is storing fine  Now i want to make a relationship but sql server 2012 does not allow me due to different data types of the columns  What would be the word around becasue if i want to change the data type to make both of them as same data type then i require  that change in many many stored procedure as well as in the code  tell me the word around  secondly as my data is storing correctly so if i do not make the relationship then what? because i am storing data using checkboxes so i am storing the correct values why to make the relationship if i do not make the relationship and what happens? as i have to create reports at the end so i need joins as well

Mark Tabor · Answer

Msg 8106 Level 16 State 1 Line 5 Table 'Tehsilss' does not have the identity property. Cannot perform SET operation. That Error comes in

Mark Tabor · Answer

Msg 8106 Level 16 State 1 Line 5 Table 'Tehsilss' does not have the identity property. Cannot perform SET operation.

Vinay Singh · Answer

Hi It says that you dont have identity column in your table. So here Region of error: The SET IDENTITY_INSERT statement allows the insertion of explicit values into the identity column of a table. The syntax of the SET IDENTITY_INSERT statement is as follows: For detail check the link http://www.sql-server-helper.com/error-messages/msg-8106.aspx Here one more solution i found plz tyr this alos http://www.sql-server-performance.com/2007/cannot-perform-set-operation/

Mark Tabor · Answer

thanks Vinay Singh When i apply your procedure it says Msg 8106 Level 16 State 1 Line 1 Table 'Tehsilss' does not have the identity property. Cannot perform SET operation. what so ever in my table i have a primary key there but it is not set as identity and auto increment

Vinay Singh · Answer

Hi As we know that bigint represent the Int64 and tinyint represent the Byte. Have a look for detail list of sql data type http://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-datatypes Now you problem solution crate new table with correct schema copy data with inentity_insert on rename tables - drop old table or try by below query Select * from table_Name where IntCol= CAST(UnsignedSmallInt AS INT)

suresh Shewale · Answer

Hello  In RDBMS you must maintain relationship.In SQL Server if datatype is different you cant get result as you expected .So its better you should keep both fields datatype same and change all stored procedure.To do fast work in store procedure 1. You can findstore procedures having those specific fields in single query and change all variables easily. 2. Also if you store bigint as primary key in one table and when value reaches to big value then tinyint cant store this value.So its better you change field type to bigint and make as foreign key. If you dont make relationship then its like open table structure and when there is large data then you cant easily create query and faster result. thanks