set column to NOT NULL not working

set column to NOT NULL not working

Post by Keith G Hi » Fri, 03 Nov 2006 01:26:53


sql 2k

table as follows:

CustPhones.PhoneID (PK)
CustPhones.CustID (FK to Custs table) - was previously set to allow nulls
and is a non unique index

I tried to do this:

ALTER TABLE CustPhones ALTER COLUMN CustID INT NOT NULL

and got this error:

Server: Msg 5074, Level 16, State 8, Line 1
The index 'IX_CustPhones_PhoneID' is dependent on column 'PhoneID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN PhoneID failed because one or more objects access
this column.

However, I can change it in EM without any trouble.

What do I need to do differently?

TIA,

Keith
 
 
 

set column to NOT NULL not working

Post by Tracy McKi » Fri, 03 Nov 2006 02:48:16


I assume that IX_CustPhones_PhoneID is an index on this table? Drop
that index, alter the column, and then recreate the index.


--
Tracy McKibben
MCDBA
http://www.yqcomputer.com/

 
 
 

set column to NOT NULL not working

Post by SU5UUDU » Fri, 03 Nov 2006 03:12:01

I don't have access to SS2K, but in SS2005 there are two things that need to
be true to change a column's nullability to NOT NULL
1 - The column cannot be included in any indexes.
2 - No null values in the column

I can only assume EM is smart enough to know to drop the index first, make
sure there are no NULL values in the column, then change the column property
to NOT NULL, and recreate the INDEX.

This is the procedure you would need to follow to do it manually.

Bob

DROP TABLE dbo.CustPhones;
GO
DROP TABLE dbo.Cust;
GO

CREATE TABLE dbo.Cust
(
CustID INT identity(1,1) PRIMARY KEY
,CustTX VARCHAR(36)
);
GO
insert into dbo.Cust(CustTX) VALUES ('one')
insert into dbo.Cust(CustTX) VALUES ('two')
insert into dbo.Cust(CustTX) VALUES ('three')
insert into dbo.Cust(CustTX) VALUES ('four')
insert into dbo.Cust(CustTX) VALUES ('five')
insert into dbo.Cust(CustTX) VALUES ('six')

CREATE TABLE dbo.CustPhones
(
PhoneID INT IDENTITY(1,1) PRIMARY KEY
,CustID INT NULL
REFERENCES dbo.Cust(CustID)
);
GO

insert into dbo.CustPhones(CustID) VALUES (1)
insert into dbo.CustPhones(CustID) VALUES (2)
insert into dbo.CustPhones(CustID) VALUES (NULL)
insert into dbo.CustPhones(CustID) VALUES (4)
insert into dbo.CustPhones(CustID) VALUES (5)
insert into dbo.CustPhones(CustID) VALUES (6)
GO

CREATE NONCLUSTERED INDEX
IX_CustPhones$CustID ON dbo.CustPhones (CustID ASC);
GO

SELECT
COLUMN_NAME
,IS_NULLABLE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'CustPhones'
ORDER BY
ORDINAL_POSITION
GO

ALTER TABLE dbo.CustPhones ALTER COLUMN CustID INT NOT NULL
GO

DROP INDEX IX_CustPhones$CustID ON dbo.CustPhones
GO

ALTER TABLE dbo.CustPhones ALTER COLUMN CustID INT NOT NULL
GO

UPDATE dbo.CustPhones SET CustID = 3 WHERE CustID IS NULL
GO

ALTER TABLE dbo.CustPhones ALTER COLUMN CustID INT NOT NULL
GO

SELECT
COLUMN_NAME
,IS_NULLABLE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'CustPhones'
ORDER BY
ORDINAL_POSITION
GO
 
 
 

set column to NOT NULL not working

Post by AlterEg » Fri, 03 Nov 2006 11:09:20

Change it in EM. Don't save the change, but instead click "Save Change
Script". You can copy the SQL and see all that SQL Server would do from EM.
I'm sure this one has to do with the index, but it's a good trick for
finding out other stuff as well.

-- Bill
 
 
 

set column to NOT NULL not working

Post by Hugo Korne » Sun, 05 Nov 2006 08:16:01


(snip)

Hi INTP56,

(Sigh) If only EM were that smart.

For changes like this, EM uses a very boneheaded approach: create new
table with the NOT NULL property, copy over existing data from old
table, drop constraints on old table, drop old table, rename new table,
recreate constraints. Not fun if there are several billions of rows in
the table.

--
Hugo Kornelis, SQL Server MVP