May 13, 2004 at 9:45 am
I have this t-sql code that works fine
alter table user_detail
add foreign key(user_id)
references user_header(user_id)
on delete cascade
It defines the foreign key with a system generated name like FK__user_deta__User___3864608B
Does anyone know the t-sql syntax for specifying a particular name for the foreign key?
May 13, 2004 at 10:20 am
here ya go Steve,
ALTER TABLE user_detail ADD CONSTRAINT
FK__user_header__user_detail FOREIGN KEY
(user_id)
REFERENCES user_header
(user_id)
NOT FOR REPLICATION
sorry 'bout the formatting. the smirking face is an open paren.
May 13, 2004 at 11:58 am
That worked great. Thank you very much Peter! I couldn't find that syntax anywhere.
Did you already know that one, or do you have a good t-sql reference source you can share?
May 13, 2004 at 1:04 pm
I did know that one. But one good source is enterprise manager. You can use the create diagram wizard to add tables to your diagram and using drag and drop create your PK - FK relationships. Then click the Save Change Script button on the toolbar (displays script in dialog box and allows you to save the change script). Aside from that, SQL Server Books Online that ships with SQL Server is a good resource.
Happy Trails
May 13, 2004 at 4:09 pm
Steve, this is actually in SQL Books Online under Alter Table. It is as follows...
ALTER TABLE table
<SNIP>
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON {filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply