Not Making Relationship between Primary and Foreign key in S

Mark Tabor

10y952

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 

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mark Tabor10y ago
Msg 8106, Level 16, State 1, Line 5
Table 'Tehsilss' does not have the identity property. Cannot perform SET operation.
That Error comes in
0
Mark Tabor10y ago
Msg 8106, Level 16, State 1, Line 5
Table 'Tehsilss' does not have the identity property. Cannot perform SET operation.
0
Vinay Singh10y ago
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/
0
Mark Tabor10y ago
thanksVinay SinghWhen 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
0
Vinay Singh10y ago
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)
0
suresh Shewale10y ago
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
0

Not Making Relationship between Primary and Foreign key in S

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

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