Adding new Table Constraints to Already Existing Tables

Below is an Sql Server script showing examples of how to create primary key, foreign key, unique and default constraints on on a table that already exists.

See here for example of how to do constraints in the CREATE TABLE statement itself.

Update: Added Index (unique)

Update 2010-01-16: Added notes about composite primary key (primary key including multiple columns) . Same with UNIQUE constraint. Added reference to another post I’ve done on similar topic

Adding Constraints After Table Creation.sql

— Drop tables if they exist
IF EXISTS (SELECT 1 FROM sys.objects  WHERE object_id = OBJECT_ID(N’dbo.Dog’))
BEGIN
    DROP TABLE dbo.Dog;
    PRINT ‘Dropped: dbo.Dog’;
END

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’dbo.DogTypes’))
BEGIN
    DROP TABLE dbo.DogTypes;
    PRINT ‘Dropped: dbo.DogTypes’;
END
GO

 

— Create tables
CREATE TABLE dbo.DogTypes (
    [Type] CHAR(1) NOT NULL
)

— Insert ‘F’at, ‘S’kinny and ‘A’norexic. :o( 
INSERT INTO dbo.DogTypes 
    SELECT ‘F’ UNION ALL SELECT ‘S’ UNION ALL SELECT ‘A’

CREATE TABLE dbo.Dog (
    DogID uniqueidentifier NOT NULL,
    [Type] CHAR(1) NOT NULL,
    [RegoNo] int NOT NULL
);
GO

 

/*
    Add Primary Key Constraints
*/
ALTER TABLE dbo.DogTypes
ADD CONSTRAINT PK_DogTypes_Type PRIMARY KEY ([Type]);

ALTER TABLE dbo.Dog
ADD CONSTRAINT PK_Dog_DogID PRIMARY KEY ([DogID]);

Note: Inside PRIMARY KEY () you can add a comma delimited list of columns for your composite key.

/*
    Add Foreign Key Constraint
*/
ALTER TABLE dbo.Dog
ADD CONSTRAINT FK_Dog_Type FOREIGN KEY ([type])
    REFERENCES dbo.DogTypes([Type])

/*
    Add Default Constraint
*/
ALTER TABLE dbo.Dog
ADD CONSTRAINT DF_Dog_DogID DEFAULT (NewSequentialID()) FOR [DogID];

/*
    Add unique constraint
*/
ALTER TABLE dbo.Dog
ADD CONSTRAINT U_Dog_RegoNo UNIQUE([RegoNo])
GO

Note: Inside UNIQUE() you can add a comma delimited list of columns

/*
    Add Clustered Index
    Note: You wouldn’t do this normally, Just as an example!!!
*/
CREATE UNIQUE CLUSTERED INDEX I_Dog_DogID
    ON [dbo].[Dog](DogID);
GO

Advertisements

One Response to “Adding new Table Constraints to Already Existing Tables”

  1. Sql Server 2005 Constraint Examples « Randomos Technos Says:

    […] Server 2005 Constraint Examples August 4, 2009 — jwwishart Note 2010-01-06: See here for examples of how to do constraint in a separate statement (not in the CREATE TABLE […]


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: