ALTER TABLE Impact to Foreign Keys, Primary Key, Default Constraints, Unique Constraints, and Indexes

So you set up a look up table using a type [integer] for the primary key?   

But there are only 15 rows and the table will never have more than 255 rows.  So why not use a [tinyint] type for the primary key instead? 

Well, if you thought of this ahead of time, great.  

If you didn't and default constraints and foreign key relationships are already set up, you have to drop those and re-create them.

Otherwise, you'll get errors:

The object 'PK_VTypeID' is dependent on column 'VTypeID'

The object 'FK_Man_VType_VType' is dependent on column 'VTypeID'.

ALTER TABLE ALTER COLUMN VTypeID failed because one or more objects access this column.

Column already has a DEFAULT bound to it.

Could not create constraint. See previous errors.

The operation failed because an index or statistics with name 'IDXUQ_Manufacturer_VehichleTypes'  ..'.

To drop a foreign key constraint

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Vehicle_Type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Vehicles]'))

ALTER TABLE [dbo].[Vehicles] DROP CONSTRAINT [FK_Vehicle_Type]

GO

To drop a default constraint

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Vehicle_VType]') AND type = 'D')

ALTER TABLE [dbo].Vehicles] DROP CONSTRAINT [DF__Veh__VType__7AC02C80]

GO

-- find the Table Name, Column Name, and Default Value given a Default Contstraint Name  --just in case the name doesn't clue you into table name and column name.

SELECT so1.name as [Default Name]

, so2.name as [Table]

, scl.name as [Column]

,df.definition as [Definition]

,df.is_system_named

FROM dbo.sysobjects so1 

INNER JOIN sysconstraints scn ON so1.id = scn.constid 

INNER JOIN sysobjects so2 ON so1.parent_obj = so2.id

INNER JOIN syscolumns scl ON scl.colid = scn.colid AND scl.id = so1.parent_obj

INNER JOIN sys.default_constraints df ON parent_column_id = scl.colid AND so1.id = df.object_id

WHERE so1.name = 'DF__Loc8__DST__430CD787'

To get a list of constraints in a table use the system stored procedure sp_helpConstraint. It's [constraint_keys] column is derived from the [definition] column from the sys.default_constraints table.

exec sp_helpConstraint Loc8

Querying the INFORMATION_SCHEMA does not return the default constraint name, and the first two commands don't even include default constrains in the result. INFORMATION_SCHEMA.COLUMNS does show the default definition/constraint_keys

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_NAME = 'DF__Loc8__DST__430CD787' OR TABLE_NAME='Loc8'

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME = 'DF__Loc8__DST__430CD787' OR TABLE_NAME='Loc8'

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Loc8'