Trouble re-creating relationship
-
I'm having trouble re-establishing a relationship between two tables. I have a foreign key table - records and a primary key table table - center. The primary key being "clinicphone" - I'm not sure exactly what happened, but when I created the tables, and the relationship, a record in the "records" table could not be created without a valid "clinicphone" - now it seems the relationship is gone and I can't recreate it! This is the error I am getting:
'center' table saved successfully
'records' table
- Unable to create relationship 'FK_records_center'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_records_center'. The conflict occurred in database 'MROresults', table 'center', column 'clinicphone'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'records' has been created but its maximum row size (12064) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Anyone have any idea how I could fix this?
Kristin
Kristin
-
Modify your table structure. Sounds like some of the columns are *really* wide. See if this helps explaining http://qa.sqlservercentral.com/columnists/sjones/pagesize.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
right....but if I try to change the length of some of my fields in the design view, I get booted.
Another strange thing is this. I copied the table and created the relationship successfully with the copy. Why do you think that is?
Kristin
-
Sorry, I don't understand. Do you mean, EM kicks you out when you try to change the table? I would bcp or DTS the data out, let EM create a script of that table (take care of the options you want to be included!!!), open Query Analyzer, make the changes to the structure, run this script and bcp or DTS the data back in.
And a verification via SELECT LEN(...), DATALENGTH(...) that you don't truncate your data yourself has also a certain charme to me here.
As for this issue with the copied table. Did you have any data in there, when you created successfully the relation? Or was it an empty table?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yeah! EM boots me, and also makes the Database Hang on all my users. Can you help with the script I would need to run in Query Analyzer?
And as for the copied table, all the data was there when I created the relation.....just none of the indexes or default values ......
Kristin
-
Sure, generate the script and post it here.
And never, ever do such things with EM. It is inefficient and in most cases drops and recreates the table behind the scenes. So, no wonder nobody's able do work while you do this operation. And if this is a large table, it takes ages to run.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply