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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy