CODEWITHSUNDEEP
mysqlsqldatabase
techPackets
techPackets

Reputation: 4506

inserting into new added columns in an existing table

I have a table 'user' already in db with fields 

create Table user (
id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
profilename varchar(20) NOT NULL,
email varchar(40) NOT NULL,
socialemail varchar(40) NOT NULL)engine=InnoDB;

The stated columns also contain values

I altered the table and added some more columns

ALTER TABLE user
ADD COLUMN enabled varchar(1),
ADD COLUMN accountnonexpired varchar(1),
ADD COLUMN credentialsnonexpired varchar(1),
ADD COLUMN accountnonlocked varchar(1);

Now when I am inserting values into new columns with the below command in MYSQL.

insert into user
(id,enabled,accountnonexpired,credentialsnonexpired,accountnonlocked) values ('1','Y','Y','Y','Y'),('2','Y','Y','Y','Y');

I am getting an error 

Error Code: 1364. Field 'username' doesn't have a default value

Can anyone tell me why? What should be the correct way to insert values in new columns?

enter image description here

Upvotes: 0

Views: 320

Answers (5)

RobP
RobP

Reputation: 9522

INSERT adds new rows to your table, and those rows would have to have a non-null username for the INSERT to succeed It's not 100% clear but I think you are saying that you want to set the values of these new columns for all your existing rows. To do that you need UPDATE not INSERT:

UPDATE user SET id='1', enabled = 'Y', accountnonexpired = 'Y' WHERE 1

I omitted a few of your columns for brevity but you get the idea. You may also want to alter the table to make these values the DEFAULT for new rows inserted in the future.

Upvotes: 4

crthompson
crthompson

Reputation: 15865

An INSERT is creating NEW records. You have a username field that is marked as NOT NULL But in your sql you are not including username and other NOT NULL fields in your statement.

Your insert would need to include all the NOT NULL fields.

insert into user(id,username,password,profilename,email,socialemail,enabled,accountnonexpired,credentialsnonexpired,accountnonlocked) 
values ('1',<username>,<password>,<profilename>,<email>,<socialemail>'Y','Y','Y','Y'),('2',<username>,<password>,<profilename>,<email>,<socialemail>'Y','Y','Y','Y');

I suspect you actually want to UPDATE here instead of insert.

An update would look like this:

UPDATE user set enabled = 'Y', accountnonexpired='Y', credentialsnonexpired='Y', accountnonlocked='Y'
FROM user
WHERE id = 1

Upvotes: 2

Sven Havgen
Sven Havgen

Reputation: 56

Your table has NOT NULL set for the [username], [password], [profilename], [email] and [socialemail] fields. You will need to provide values while NOT NULL has been set and there is no default value.

Unless your intention is to insert data into pre-existing columns, then use the Update statement. 

 update user
    set enabled = 'Y', accountnonexpired='Y', credentialsnonexpired='Y', accountnonlocked='Y'
    from user
    where id = 1

Upvotes: 3

Abhith
Abhith

Reputation: 306

In your insert procedure, there is no value assigned for username field, Since username varchar(20) is NOT NULLable, you need to set a default value for that col or alter the col property to accept null values.

Upvotes: 0

balyanrobin
balyanrobin

Reputation: 91

Give value for 'username' field. it has no default value and default is not null as per your table definition. This will work

Upvotes: 0

Related Questions