Foreign Key Name

  • 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?

     

     

  • 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.

  • 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?

  • 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

  • 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.

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

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