Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

A place to share commonly used scripts for SQL Server Database Administrators.

Moderators

SQL Server DBA Scripts

Script to create a Foreign key in SQL Server

Apr 13 2012 12:00AM by Paresh Prajapati   

We all know the way to create foreign key on column, and we are using UI to create it mostly instead of script. Did you used script to add foreign key?

Here I am sharing the script to add foreign key on new/existing table/column and consider RefColumn is a primary key column on RefTable table.

Creating foreign key constaint while creating a new table

CREATE TABLE ChildTable
(
    Id int not null identity(1,1),
    ChildColumn int 
    CONSTRAINT fk_RefId 
    	FOREIGN KEY REFERENCES RefTable(RefColumn)
)

-- OR

CREATE TABLE ChildTable
(
    Id int not null identity(1,1),
    ChildColumn int ,
    CONSTRAINT fk_RefId
    	FOREIGN KEY(ChildColumn) REFERENCES RefTable(RefColumn)
)

Adding a new column with foreign key constraint on existing table

ALTER TABLE ChildTable
ADD ChildColumn int 
    CONSTRAINT fk_RefId 
    FOREIGN KEY REFERENCES RefTable(RefColumn)

Adding a foreign key constraint on existing column

ALTER TABLE ChildTable
ADD CONSTRAINT fk_RefId 
    FOREIGN KEY(ChildColumn) REFERENCES RefTable(RefColumn)

Hope you liked these scripts.


Paresh Prajapati
6 · 23% · 7454
9
 
1
 
0
Failed
 
0
Lifesaver
 
 
0
Unwise



Submit

2  Comments  

  • You can also specify ON DELETE and/or ON UPDATE rules while creating FOREIGN KEY constraints using script:

    ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

    ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

    For example:

    CREATE TABLE ChildTable
    (
        Id int NOT NULL IDENTITY(1,1),
        ChildColumn int ,
        CONSTRAINT FK_RefId
            FOREIGN KEY(ChildColumn) REFERENCES RefTable(RefColumn) ON DELETE SET NULL ON UPDATE CASCADE
    )
    
    commented on Apr 16 2012 4:38AM
    Olga Medvedeva
    66 · 3% · 896
  • The Table designer UI is a good stepping stone, but should not be used to generate production code (there are a couple of drawbacks to the method).

    We always use T-SQL scripts to do most of the work (except for the application developers and the newer team members, who still use the UI), and now find it to be a faster and easier way to get work done.

    Thank-you for sharing these scripts. I am sure they would be useful to all.

    @Olga: Thank-you for extending the post.

    commented on May 13 2012 12:08PM
    Nakul Vachhrajani
    4 · 36% · 11645

Your Comment


Sign Up or Login to post a comment.

"Script to create a Foreign key in SQL Server" rated 5 out of 5 by 9 readers
Script to create a Foreign key in SQL Server , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]