Change FK error: "Unable to delete relationship..."

Hello,
I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.
The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes
Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an erro
r
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:
'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ''
[Microsoft][ODBC SQL Server Driver][SQL Server]'''
(The ' result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)
Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.
Many thanks in advance,
JustinJustin Little (JustinLittle@.discussions.microsoft.com) writes:
> Recently, there was a problem in our application when changing the
> "StudentNumber" for a Student. This is the primary key and so I get an
> error because of the relationship. The fix I want to apply is to change
> the foreign key FK_StudentIsInClass_Students. I need to turn on the
> function of "Cascade Update Related Fields". I use the GUI functions in
> SQL Server Enterprise Manager to do this but, when I try to save the
> change, I get an error:
> 'Students' table
> - Unable to delete relationship 'FK_StudentIsInClass_Students'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]'FK_StudentIsInClass_Students' ''
> [Microsoft][ODBC SQL Server Driver][SQL Server]'''
> (The ' result because my SQL Server is running on a foreign language
> server, so I can't read the exact error text.)
> Does anyone have a clue why I would get this error? Any suggestions for
> what to do about it? I appreciate any advice and help.
It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?
Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.
DECLARE @.err int
BEGIN TRANSACTION
ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @.err = @.@.error
IF @.err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @.err = @.@.error
END
IF @.err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)
I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".
The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。
The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.
This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)
If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)
Do you have any idea why I'm getting this confusing message?
Many thanks for your support.
Justin
PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you
think that this could be the cause of my current problems?|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> The error number I get is Msg 3728.
> Server: Msg 3728, Level 16, State 1, Line 1
> 'dbo.FK_StudentIsInClass_Students' ''
> The server, as you guessed, does not use a Latin script. It's Chinese,
> so the error message comes through in Chinese. On Query Analyzer I can
> see the Chinese characters instead of a bunch of question marks. Books
> Online gives the English message as: '%.*ls' is not a constraint.
Now, that was an interesting message...

> PS: I'm connecting to the database using a user id called "bigbyte",
> supplied to me by the ISP. The tables all belong to this user id (e.g.
> "bigbyte.StudentIsInClass"), however I notice that the constraints are
> members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do
> you think that this could be the cause of my current problems?
Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.
Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.
You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.
So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 6
3
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的_物件
_為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.
HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?
When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all fo
r
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data t
o
my "dev" database and I asked them to change the ownership back to "bigbyte"
.
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".
Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?
Many thanks,
Justin
"Erland Sommarskog" wrote:

> Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> Now, that was an interesting message...
>
> Most probably. I've tried to recreate this situation, but I was not
> successful. When I create tables owned by a non-dbo user, and set up
> a constraint, the constraint is owned by that user. The same happens
> if I create the tables as owned by dbo, and then change ownership.
> Maybe there was a bug in some previous version/service pack of SQL Server
> that could cause this situation.
> Since this is an abnormal situation, it is difficult to give sugestions
> that I know work. I would try to change ownership of the table to
> dbo, and then back to bigbyte.
> You may have to talk to your ISP about this, as you may need someone
> with dbo rights to perform this operation.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> I got dbo rights from the ISP for the day. I tried what you suggested
> about changing the ownership of the table. Now I'm no SQL Server guru
> but I found out that the stored procedure for doing this is called
> sp_changeobjectowner.
> So I tried running the following command:
> EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
> I get an error message saying:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 63 ' 'StudentIsInClass' '''' 'dbo' ''
> or in English: Cannot change owner of object '%ls' or one of its child
> objects because the new owner '%ls' already has an object with the same
> name.
> HOWEVER, when I try to run the same sp on a table that has no
> constraints, it runs through just fine and changes the ownership. Could
> this mean that the database has some inconsistencies in the constraints
> that it is storing?
You bet!
At this point, I would create a new set of tables copy all data over to
the new tables.
If you don't have a script saved for creating the tables, you can use
the Enterprise Manager to generate scripts. Make sure that you get triggers,
indexes and foreign keys.
Now, let's see what is the best way to have these two sets of tables in
parallel. I think the best is that youy edit the script so that all
table and trigger names are prefixed by "guest.". Constraints should not
be prefixed. Then use INSERT to copy data over. Take the occassion to add
ON UPDATE CASCADE.
Once this is done drop the existing tables - and let's hope *that* is
possible.
Finally, run sp_changeobjectowner to change ownership to bigbyte or dbo,
which ever you feel best.

> When I first created these tables (about a year or more ago) I used the
> "bigbyte" user. Later, I noticed that all the ownerships had changed to
> "dbo". I guessed that the ISP had been mucking around and changed it
> all for reasons best known to them. (Like I said, I'm no guru so I
> don't know what all this ownership stuff is really about anyway.)
Ownership of objects is like ownership of files in a file system.
But there is a second side of the coin. In SQL 2000, an owner is also a
schema. (This changes in SQL 2005). A schema is essentially a namespec.
A user has a default schema, which in SQL 2000 is always equal to his
user name. When you say "SELECT * FROM tbl" without specifying schema/owner,
SQL Server first looks in your default schema, then in the default schema of
the database, which in SQL 2000 always is dbo.
The guest user is a pre-defined user, and also has a schema, which I
suggested that you should use above, to permit you to have two sets of
tables at the same time.
I should add that in SQL 2000, having all objects owner by dbo - and thus
in the dbo schema - is what people do 99% of the time.

> Is there a stored procedure or something I can use to try to track down
> multiple instances of the same constraint name, possibly owned by
> different owners? Perhaps my original constraints are still bouncing
> around somewhere inside the db's system tables, unknown to all of us?
You would have to run queries on sysobjects to find out. But this database
seems a bit mashed, so you will need some brute force to get it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx