September 12, 2002 at 7:02 am
I have a few columns with datatype int on a table and somehow they get the value '0' whenever I enter a new record with empty values for that columns and I don't have any Default Value or Constraint for these columns. Is this a designed behaviour of SQL Server? Do every column with int datatype get the default value 0 everytime they left empty? How can I prevent this?
September 12, 2002 at 12:50 pm
Are those columns NULLABLE? I think if it's NULLABLE then when you insert nothing into it it gets a NULL, otherwise you can always specify a DEFAULT value.
ALTER TABLE sales
ALTER COLUMN qty INT NULL
DEFAULT NULL WITH VALUES
Will that work?
September 12, 2002 at 1:54 pm
Did you check for any triggers on that table?
I don't think what you say is design behavior of SS. See below:
SET NOCOUNT ON
GO
USE TEMPDB
GO
PRINT 'Creating a table with no defaults on any columns...'
CREATE TABLE TABLE_TEST_OUT_INT(A_TEXT_FIELD VARCHAR(5), AN_INT_FIELD INT);
GO
PRINT 'Inserting values into the table...'
INSERT INTO TABLE_TEST_OUT_INT(AN_INT_FIELD) VALUES(1);
INSERT INTO TABLE_TEST_OUT_INT(A_TEXT_FIELD) VALUES('Hello');
GO
PRINT 'Displaying all records in the table...'
PRINT ''
SELECT * FROM TABLE_TEST_OUT_INT;
PRINT '(note: there are no zeroes in the int field)'
GO
PRINT 'Dropping the table...'
DROP TABLE TABLE_TEST_OUT_INT
GO
SET NOCOUNT OFF
September 15, 2002 at 4:09 am
nickel01:
yes they're nullable
bp:
no i don't have any triggers.
I found out something today. After changing the datatype of the column to varchar I was still getting 0 value inserted. The stored proc I use to enter data to this column had @zipcode int=null parameter for it. After changing it to @zipcode varchar(50)=null it stopped enter 0 for default. I couldn't figured it out what to do other than using varchar datatype, I wonder what I should do for this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply