0

I am using MySQL, I have a table called student as given below, 

Name       Idnumber
'tony'        1
'jimmy'       2

I am going to add one more column using the below query

alter table student add location varchar(20) not null;

Now, if I use the query,

update student set location=null where name='tony'

the value of null is being inserted into the table. Below was the table I got.

Name       IdNumber         location
'tony'       1                 null
'jimmy'      2

Since I had set location as not null while altering the table, why is it still accepting null? Also, if I tried to insert null using, 

insert into student(Name,IdNumber) values('robert',3);

the query is not demanding a not null value for location. 'Not Null' constraing is only working when I use the query, 

insert into student (Name,IdNumber,location) values('john',8,null);

Please tell my why 'not null' constraint is not working in the other cases described above.

Thanks, Vivek G

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
Vivek G Benakanakere
  • 1
  • It is not working because there are already rows on your table. I think mysql in this case ignores that constraint. Not sure though. – Jorge Campos Dec 15 '15 at 12:17
  • When you create a column it will have null values by default so you can't create a column with a constraint that will be bypassed. Others RDBMs will give you an error about this. – Jorge Campos Dec 15 '15 at 12:18
  • Possible duplicate of [I'm looking for a constraint to prevent the insert of an empty string in MySQL](http://stackoverflow.com/questions/2514178/im-looking-for-a-constraint-to-prevent-the-insert-of-an-empty-string-in-mysql) – Thernys Dec 15 '15 at 12:21

0 Answers0