September 30, 2014 at 1:35 am
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Patient System Number] [varchar](10) NOT NULL,
[District Number] [varchar](14) NULL,
[Episode Number] [int] NOT NULL,
[Casenote Number] [varchar](14) NULL,
[Surname] [varchar](35) NULL,
[Title] [varchar](5) NULL,
[Forename] [varchar](35) NULL,
[NHS Number] [varchar](17) NULL,
[NHS Number Status] [varchar](2) NULL,
[Date of Birth] [datetime] NULL,
[Sex] [varchar](1) NULL,
[Marital Status] [varchar](1) NULL,
[Ethnic Origin] [varchar](4) NULL,
[Address Line 1] [varchar](35) NULL,
[Address Line 2] [varchar](35) NULL,
[Address Line 3] [varchar](35) NULL,
[Address Line 4] [varchar](35) NULL,
[Postcode] [varchar](10) NULL,
[New HA Code] [varchar](3) NULL,
[Registered GP] [varchar](8) NULL,
[Registered Practice Code] [varchar](6) NULL,
[Appointment Date] [smalldatetime] NULL,
[Appointment Time] [smalldatetime] NULL,
[Referral Date] [smalldatetime] NULL,
[Referral Source] [varchar](3) NULL,
[LeadClinician] [varchar](8) NULL,
[ServiceGroup] [varchar](4) NULL,
[Referring GP] [varchar](8) NULL,
[Referring Practice] [varchar](6) NULL,
[Prev DNA/CAN] [smalldatetime] NULL,
[ProviderCode] [varchar](5) NULL,
[Contract Id] [varchar](6) NULL,
[Purchaser] [varchar](5) NULL,
[Contract Group] [varchar](4) NULL,
[Religion] [varchar](4) NULL,
[CensusDate] [smalldatetime] NOT NULL,
[WaitingTime(Weeks)QM08] [int] NULL,
[WaitingTime(Weeks)] [int] NULL,
[ReferralKey] [varchar](40) NULL,
[ClinicCode] [varchar](8) NULL,
[DataSource] [tinyint] NOT NULL,
[PCGOfResidence] [varchar](5) NULL,
[Next DNA/CAN] [smalldatetime] NULL,
[BookingType] [varchar](4) NULL,
[PCGOfRegGP] [varchar](5) NULL,
CONSTRAINT [PK_DTb_HWSQueueMonthEnd] PRIMARY KEY NONCLUSTERED
(
[Patient System Number] ASC,
[Episode Number] ASC,
[CensusDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
DECLARE @addc varchar(4000)-- Add contraint script
DECLARE @tb_Name varchar(1000)-- The name of the table where the field needs to be altered
DECLARE @const varchar(1000)-- The name of the constriant
DECLARE @pos INT -- The ordinal position of the field
DECLARE @tbl_const TABLE (tbl_name varchar(100),const_name varchar(255))--Table to build up constraint
SET @Column = 'Patient System Number'
SET @size ='20'
BEGIN TRY
BEGIN TRANSACTION
-- Get constraint data
-- To enable recreation of contraints
DECLARE constraint_cursor CURSOR FOR
SELECT k.Table_name, k.column_name,k.ordinal_position
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
Information_schema.TABLES t
ON
c.TABLE_NAME = t.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON
k.table_name = t.table_name
WHERE
c.COLUMN_NAME = @Column
AND
t.TABLE_TYPE = 'Base Table'
ORDER BY
k.TABLE_NAME,
k.ordinal_position
--We now need to concatenate the constraint fields for each table
OPEN constraint_cursor
SET @pos = 1
FETCH NEXT FROM constraint_cursor INTO @tb_Name,@const,@pos
WHILE @@FETCH_STATUS = 0
BEGIN
IF @pos = 1
BEGIN
INSERT INTO @tbl_const values(@tb_name,'[' + @const + '] ASC')
END
ELSE
BEGIN
UPDATE @tbl_const SET const_name = const_name +','+ '[' + @const + '] ASC'
WHERE tbl_name = @tb_Name
SET @pos = @pos + 1
END
FETCH NEXT FROM constraint_cursor INTO @tb_Name,@const,@pos
END
CLOSE constraint_cursor
DEALLOCATE constraint_cursor
DECLARE db_cursor CURSOR FOR
SELECT '[' + c.Table_Schema+'].['+c.Table_Name
+']' AS 'Object',
'ALTER TABLE [' + c.Table_Schema+'].['+c.Table_Name
+'] DROP CONSTRAINT ' + k.Constraint_Name ,
CASE WHEN i.type = 2 THEN
'ALTER TABLE [' + c.Table_Schema+'].['+c.Table_Name
+'] ADD CONSTRAINT ' + k.Constraint_Name + ' PRIMARY KEY NONCLUSTERED ('+ n.const_name + ')
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])'
ELSE 'ALTER TABLE [' + c.Table_Schema+'].['+c.Table_Name
+'] ADD CONSTRAINT ' + k.Constraint_Name + ' PRIMARY KEY CLUSTERED ('+ n.const_name + ')
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])'END,
'ALTER TABLE [' + c.Table_Schema+'].['+c.Table_Name
+'] Alter Column ['+c.Column_Name+'] varchar('
+@size+')'
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
Information_schema.TABLES t
ON
c.TABLE_NAME = t.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON
k.table_name = t.table_name
INNER JOIN
@tbl_const n
ON
n.tbl_name = c.TABLE_NAME
INNER JOIN
sys.indexes i
ON
i.name = k.Constraint_Name
WHERE
c.COLUMN_NAME=@column
AND
t.TABLE_TYPE = 'Base Table'
AND
k.column_name = @column
AND
LEFT(k.Constraint_Name,2) = 'PK'
GROUP BY
c.Table_Schema,
c.Table_Name,
c.Column_Name,
k.Constraint_Name,
k.column_name,
n.const_name,
i.type
ORDER BY c.TABLE_NAME
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Object,@dropc,@addc,@TSQL
-- Now Set Transaction
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Object+','+ @dropc+','+ @TSQL + @addc
EXEC (@dropc)
EXEC(@TSQL)
EXEC(@addc)
FETCH NEXT FROM db_cursor INTO @Object,@dropc,@addc,@TSQL
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
@Object
ROLLBACK TRANSACTION
END CATCH
CLOSE db_cursor
DEALLOCATE db_cursor
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
.
None of the fields I try to create the key on are nullable.
Please can someone throw some light on this.
September 30, 2014 at 1:54 am
When you are running the following:-
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
The following is the issue:-
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
By default, it is being set as:-
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10) NULL
Try running:-
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10) NOT NULL
September 30, 2014 at 2:10 am
Great thanks very much, default values doh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply