Unanswered: Cannot update ID because of no existing relationship
I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.
For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.
The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same????! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.
Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?
Or is there a command that tells MSSQL to ignore all relationships for the next query?
I suspect that the data is stored in the column without the trailing spaces, although it may be presented with spaces added. This is common when retrieving data in columnar form. To test this, retrieve an expression rather than the raw column to see what you actually get, something like:
SELECT myColumn, '[' + myColumn + ']'
The real relationship information is stored in system tables in the form of constraints. The graphic representation is easier to understand, but the constraints are the "final answer" in terms of the relationships that MS-SQL enforces.