'Default' is not inserting values while creating tables.

Question

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?

Prabhu Raja · Answer

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

Aarti · Answer

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 Ahmed · Answer

@ 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 Nair · Answer

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 Krishnamurthy · Answer

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