SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Enter a row with Null		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Adam West
Constraint Violating Yak Guru

USA
261 Posts
Posted - 08/04/2009 :  12:09:20  Show Profile  Reply with Quote
I have a table of colors for use in a chart.
Right now, not all categories have a color but I want the categories entered into the table with the color as blank but I am getting this error

Cannot insert the value NULL into column 'Color', table 'CDEV_MODS.dbo.Colors'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3426 Posts
Posted - 08/04/2009 :  12:20:17  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
post your table definition.

How are you implementing the [colors] column? A foreign key to a colours table? or are you denormalised (using VARCHAR and 'BLUE', 'RED') etc.

I'm guessing your column definition doesn't allow nulls (is it the primary key of the table?)


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Edited by - Transact Charlie on 08/04/2009 12:21:49
Go to Top of Page

Adam West
Constraint Violating Yak Guru

USA
261 Posts
Posted - 08/04/2009 :  12:25:53  Show Profile  Reply with Quote
Description (varchar (50) not null)
Color (varchar (50) not null)

no keys or indexes. This was setup by an outside developer. What happened was I deleted the rows by accident and have to recreate this.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3426 Posts
Posted - 08/04/2009 :  12:32:19  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
your column definition says NOT NULL. you can't put NULLS in it.

Check out the ALTER TABLE statement in books online to change this..

You should still be able to put the empty string '' instead but NULL is much more suitable for Not / appropriate / Unkown

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Edited by - Transact Charlie on 08/04/2009 12:33:14
Go to Top of Page

Adam West
Constraint Violating Yak Guru

USA
261 Posts
Posted - 08/04/2009 :  13:48:01  Show Profile  Reply with Quote
I tried this but it doesn't compile

ALTER TABLE colors
MODIFY color varchar(50) null

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3426 Posts
Posted - 08/05/2009 :  05:09:17  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
your syntax is wrong.

example:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL DROP TABLE #foo
GO

CREATE TABLE #foo (
	[id] INT IDENTITY(1,1)
	, [val] VARCHAR(50) NOT NULL
	)
go

INSERT #foo ([val])
VALUES (NULL)
GO

ALTER TABLE #foo ALTER COLUMN [val] VARCHAR(50) NULL
GO

INSERT #foo ([val])
VALUES (NULL)
GO

SELECT * FROM #foo



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000