/******************************************************
 *
 * Name:         basic-training-default.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     12-21-2012
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about default values.
 *
 ******************************************************/


--
-- Select training database
--

-- Which database to use.
USE [TRAINING]
GO


--
-- Remove column?
--

-- Remove column if it exists
IF EXISTS (
    SELECT * FROM sys.objects o join sys.columns c ON o.object_id = c.object_id
    WHERE o.name = 'PUBLISHERS' and o.type = 'U' and c.name = 'zip'
)
ALTER TABLE [PUBS].[PUBLISHERS] DROP COLUMN zip;
GO


--
-- Unnamed constraints
--

-- Add constraint w/o name
ALTER TABLE [PUBS].[PUBLISHERS]
ADD zip char(5) NOT NULL DEFAULT('02910');
GO

-- Remove the constraint
ALTER TABLE [PUBS].[PUBLISHERS] DROP CONSTRAINT [DF__PUBLISHERS__zip__02084FDA];
GO

-- Remove column
ALTER TABLE [PUBS].[PUBLISHERS] DROP COLUMN zip;
GO


--
-- Named constraints
--
   
-- Add constraint with name
ALTER TABLE [PUBS].[PUBLISHERS]
ADD zip char(5) NOT NULL CONSTRAINT DF_LOCAL_ZIP DEFAULT('02910');
GO

-- Remove the constraint
ALTER TABLE [PUBS].[PUBLISHERS] DROP CONSTRAINT [DF_LOCAL_ZIP];
GO

-- Remove column
ALTER TABLE [PUBS].[PUBLISHERS] DROP COLUMN zip;
GO


--
-- Adding constraint after the fact
--

-- Add column that allows nulls
ALTER TABLE [PUBS].[PUBLISHERS] ADD zip char(5) NULL;

-- Add constraint
ALTER TABLE [PUBS].[PUBLISHERS] ADD CONSTRAINT DF_LOCAL_ZIP DEFAULT '02910' FOR zip;
GO


--
-- With values clause
--
   
-- Add constraint with name, with values clause
ALTER TABLE [PUBS].[PUBLISHERS]
ADD zip char(5) NOT NULL CONSTRAINT DF_LOCAL_ZIP DEFAULT('02910') WITH VALUES;
GO