Alter foreign key constraint
-
Let's say you create a table with a foreign key constraint declared, but forget the cascade delete (and want it).
You've got the table in place and populated and want to add that constraint.
It's easy to change the create table syntax to just add "on delete cascade".
It's easy enough to drop the constraint and add a new foreign key constraint, but this one is named (and SQL knows you named it).
If you later create another database with the create table with the "on delete cascade" on the foreign key declaration (as opposed to a separate add-constraint), and compare the two databases, they come out different.
Is there a way to alter an existing table, adding a "on delete cascade", but leaving the table and database in the same state as if it were there all along, e.g. so if you compare to one where it was created initially they match?
-
Haven't done this much, so usually I just use EM oto do it. Not really sure what all it does to add it but if yuo want to know setup a dummy table in a test enviroment and make the change while running Profiler to get a trace of the calls.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Couldn't you just drop the constraint and use ALTER TABLE to add the constraint back in with the specified constraint name (this is what EM does if you add cascading delete in the table designer). For example:
Given: 1) Foreign Key constraint named "MyForeignKey"
2) Foreign Key is on table called "TableB" and on a column called "ForeignKeyColumn"
3) Foreign Key points to a primary key called "MyPrimaryKey" that resides in a table called "TableA" and contains a column "PrimaryKeyColumn"
Execute the following:
ALTER TABLE TableB
DROP CONSTRAINT MyForeignKey
GO
ALTER TABLE TableB
ADD CONSTRAINT MyForeignKey FOREIGN KEY (ForeignKeyColumn)
REFERENCES TableA (PrimaryKeyColumn)
ON DELETE CASCADE
GOWill this not work for what you are doing?
Matthew Burr
Edited by - mdburr on 10/14/2002 4:09:32 PM
-
I should have been more specific. Here's an example in code.
Let me first explain that as we deploy an application on multiple servers, we do a couple things to make sure they stay in synch:
1) Require programmers to maintain a set of database creation scripts.
2) Also require ALTER scripts to apply changes to existing machines
3) Periodically create a "master" database from the database creation scripts (e.g. new install, or just to test), and then do a compare (e.g. redgate) with production databases to ensure they have been kept in synch.
So it's important to us that the table creation scripts be kept updated.
Consider the following:
create table parent (Parentname varchar(10) primary key)
create table child (ParentName varchar(10), ChildName varChar(10)
primary key (ParentName, ChildName)
foreign key (ParentName) references Parent (ParentName))
Let's say we created that and had it all working and data populated and in production. If I script that I get substantially the same statements (notably the foreign key constraint does not show a name). Here's what the object browser scripts CHILD as after the above create:
CREATE TABLE [child] (
[ParentName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ChildName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[ParentName],
[ChildName]
) ON [PRIMARY] ,
FOREIGN KEY
(
[ParentName]
) REFERENCES [parent] (
[Parentname]
)
) ON [PRIMARY]
END
GO
Now I went into enterprise manager and added the delete and update cascade:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[child]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [child] (
[ParentName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ChildName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[ParentName],
[ChildName]
) ON [PRIMARY] ,
CONSTRAINT [FK__child__ChildName__24927208] FOREIGN KEY
(
[ParentName]
) REFERENCES [parent] (
[Parentname]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
END
GO
Notice that now the constraints are explicitly named. I'm not really so much trying to say that is a problem per se, but the problem is that now the original script, modified with the cascade:
create table child (ParentName varchar(10), ChildName varChar(10)
primary key (ParentName, ChildName)
foreign key (ParentName) references Parent (ParentName) on delete cascade on update cascade)
no longer produces an equivilent table. If I run this script on a new database and do a comparison later against on that had the alter done, they fail to compare. Now some human looking closely at the difference will see they are equivilent, but it's a pain.
The only alternative I've found is not script the original table in the shorthand above, but instead to put in the foreign key as explicit named constraints in separate alter statements after the table create. Which isn't bad, just a lot more verbose (and also not directly compatible with code written, for example, by visio).
What I really want to know is how to say, when adding the constraint, "I'm giving you a name but it's not really a name, just as sQL did if I added it when we created the table".
I realize this is a subtle point and confusing, apologes in advance. Advice welcomed.
-
hi,
What is wrong with explicit giving a constraint a name, this makes scripting maintenance easier.
regards,
Klaas-Jan
-
Ah, I see. You have created a bit of a conundrum for yourself by not explicitly naming your foreign keys; SQL Server, of course, provides its own automatic name for them, which you can see by using "sp_help" to view information on your child table.
If you must insist upon the practice of not explicitly naming the foreign keys, you will of course create extra work for yourselves to do in order not to receive a complaint when comparing the schemas. You will have to go to your new server, find out the name of the foreign key that SQL Server generates there, then return to your original server, drop the foreign key constraint, and add a new one, using ALTER TABLE and explicitly providing a name for it, which in this case will be the name of the foreign key as it appears on the new server; at that time, you could also add whatever additional options you desire on the foreign key. This in contradistinction to providing explicit names for your foreign keys when you create them which gives you not only the advantage of not having to go through so much extra work, but also the advantage of providing meaningful names for your foreign keys that better express the logical relationship between your parent and child entities.
Personally, given the advantages, I would choose the latter path, but I realize that it may entail some initial work on your part to go back through your schema and provide explicit foreign key names; frequently, this up-front work is viewed as an inconvenience, and there is little you can do to persuade others that the initial inconvenience provides long-term benefits. Oh, well; I suppose most of us suffer with that one from time to time.
Matthew Burr
Edited by - mdburr on 10/15/2002 11:04:29 AM
Edited by - mdburr on 10/15/2002 11:05:46 AM
-
I guess I was hoping for the
ALTER TABLE x ALTER CONSTRANT MAKE UNNAMED
statement.
Naming them is straightfoward, and probably should start. We were doing them this way because that's the default for code that we were having generated, but a bit more time up front may save trouble later I guess.
Oh, all those existing databases to change.....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply