Foreign Key Name

Question

Post reply

Foreign Key Name

Steve Dison

Valued Member

Points: 72
More actions
May 13, 2004 at 9:45 am

#179642

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Peter P SSCrazy Points: 2777 More actions · Answer 1

Peter P

SSCrazy

Points: 2777

May 13, 2004 at 10:20 am

#506349

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.

Steve Dison Valued Member Points: 72 More actions · Answer 2

Steve Dison

Valued Member

Points: 72

May 13, 2004 at 11:58 am

#506363

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?

Peter P SSCrazy Points: 2777 More actions · Answer 3

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

gljjr SSCrazy Eights Points: 9970 More actions · Answer 4

Steve, this is actually in SQL Books Online under Alter Table. It is as follows...

Syntax

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.