'Default' is not inserting values while creating tables.

Syed Arbab Ahmed

The table has been created through the query below

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Karachi',
Country char(25),
Birth_Date date)

but the default value is not inserting values in their respective columns, what's wrong with the query?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prabhu Raja15y ago
Hi,

The Default value will be inserted into the column, if you perform insertion against the table by leaving the column.

// Here i doesn't include Address, City column
INSERT INTO Customer(First_Name,Last_Name ,Country,Birth_Date) VALUES('xyz','xyz','pakistan','01/01/2011');

So the Default value unknown and karachi will be assigned to Address and city column. You can see this by,

Select * from customer
Aarti15y ago
Hi Syed,

You are given here only script for Table creation.. that is good.

As you are not mention here your insert Script.

i think you are using script for inserting data like below,. tds why its not taking default value. because for default value you should insert blank .
INSERT INTO Customer(First_Name,Last_Name ,Address,City,Country,Birth_Date) VALUES('xyz','xyz','xyz','pune','India','01/01/2011');

to insert Default value of Address Column in customer use below sql
INSERT INTO Customer(First_Name,Last_Name ,Address,City,Country,Birth_Date) VALUES('xyz','xyz','pune','India','01/01/2011');

check with above may be it will help you.

Thanks.
Syed Arbab Ahmed15y ago
@ disel2010: You mean to say that 'Default' does not work, we need to have insert query to fill the columns, I am learning Oracle/SQL commands from a website where I found this:
Sometimes, we want to provide a default value for each column. A default value is used when you do not specify a column's value when inserting data into the table. To specify a default value, add "Default [value]" after the data type declaration. In the above example, if we want to default column "Address" to "Unknown" and City to "Mumbai", we would type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)
Referece:
http://www.1keydata.com/sql/sqlcreate.html
Thanks in advance
Suthish Nair15y ago
Its working fine for me..

USE [MyDBSuthish]
GO

/****** Object: Table [dbo].[customer] Script Date: 03/24/2011 21:56:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[customer](
[First_Name] [char](50) NULL,
[Last_Name] [char](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[customer] ADD DEFAULT ('Unknown') FOR [Last_Name]
GO

insert into customer (First_Name) values ('Name1')
insert into customer (First_Name) values ('Name2')

select * from customer
Raja Krishnamurthy15y ago
You have not given Insert Query. Your table looks good. Check the insert statement which inserts data into your table and see if you have left the address and city columns while inserting. If you have included them then those values are going to be inserted.

HTH.

Happy Programming!!!

Regards,
Raja