0

I need to edit a datatype (IS_NULLABLE) from my column tag. Nothing works, i have tried like that:

ALTER TABLE veille
ALTER COLUMN tag
SET IS_NULLABLE false

or like that :

ALTER TABLE veille ALTER COLUMN tag Modify datatype false

but doesn't work too, i have this error : 

 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS_NULLABLE false' at line 3.

Edit : Resolved, the problem was my column has null values, i have changed these null values and all works fine.

Ygg69
  • 261
  • 6
  • 22
  • 1
    What is the error ? – nimour pristou Dec 19 '16 at 09:31
  • `ALTER TABLE veille ALTER COLUMN tag SET NOT NULL` what if you try this? – WasteD Dec 19 '16 at 09:35
  • 1
    Haven't used squirrel but shouldn't the sql look something like this? `ALTER TABLE veille ALTER COLUMN tag datatype NOT NULL` – t1f Dec 19 '16 at 09:35
  • This has nothing to do with Squirrel, but with the database you are using. Which DBMS is that? But there is no DBMS that has an error message that says "*doesn't work*". [edit] your question and add the **exact** error message you get. –  Dec 19 '16 at 09:46
  • i have as error : Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS_NULLABLE false' at line 3. I have the same error with : ALTER TABLE veille ALTER COLUMN tag SET NOT NULL and with : ALTER TABLE veille ALTER COLUMN tag datatype NOT NULL – Ygg69 Dec 19 '16 at 10:08
  • [edit] your question. Don't post additional information in comments (Did you do what the error message suggests? Read the manual?) –  Dec 19 '16 at 10:23

3 Answers3

0

try ALTER TABLE veille_ndd_hist MODIFY tag bit(1) NOT NULL DEFAULT b'0;

you may also try ALTER TABLE veille_ndd_hist CHANGE COLUMN tag tag BIT NOT NULL

Note that when using MODIFY, you need to specify the full column definition, including DEFAULT value if was defined.

side note: of course make sure that the column does not have any null values

Stephen
  • 1,532
  • 1
  • 9
  • 17
  • @Ygg69 what is the exact error and your axact syntax? – Stephen Dec 19 '16 at 13:13
  • error is : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS_NULLABLE **NOT NULL** and my syntax is : – Ygg69 Dec 19 '16 at 13:27
  • sry fail and can't edit my post, my syntax is : ALTER TABLE veille_ndd_histo MODIFY tag IS_NULLABLE **NOT NULL** – Ygg69 Dec 19 '16 at 13:28
  • @Ygg69 run this **SHOW CREATE TABLE veille_ndd_histo**. and copy your column definition, then add the **NOT NULL** – Stephen Dec 19 '16 at 13:32
  • Something like that ? SHOW CREATE TABLE veille_ndd_hist ALTER COLUMN tag BIT NOT NULL ? – Ygg69 Dec 21 '16 at 10:42
  • @Ygg69 the SHOW CREATE TABLE... was just to assist you to get the correct column definition – Stephen Dec 21 '16 at 11:01
  • ok thanks, ALTER TABLE veille_ndd_hist MODIFY tag BIT NOT NULL; modify my field 'column default' who is my default value, but i need to modify my field 'IS_NULLABLE', i need for that to modify tag then add set 'IS_NULLABLE' ? – Ygg69 Dec 21 '16 at 12:21
  • @Ygg69 you do not have to explicitly update 'IS_NULLABLE'. the value of 'IS_NULLABLE' is **true** if your column definition doesn't have the **NOT NULL** part. and **false** if you have the **NOT NULL** part which I assume that is your desired result – Stephen Dec 21 '16 at 12:53
  • I need to set the value 'NO', its for that i try to update this explicitly – Ygg69 Dec 21 '16 at 13:02
  • @Ygg69 do you want tag to accept nulls or not accept nulls? – Stephen Dec 21 '16 at 13:18
  • My colomn tag is boolean, true or false, the default value is false, and field 'is_nullable' is for not accept null. But in the bdd, in this field 'is_nullable' all others column are already on 'yes' or 'no', so i can't set true or false. Sorry if my english is bad – Ygg69 Dec 21 '16 at 13:27
  • @Ygg69 sorry but I don't quite understand. lets forget about 'IS_NULLABLE' for a minute. How do you want **tag** to treat null values? do you want to accept or fail on NULL values? – Stephen Dec 21 '16 at 13:37
  • I would like fail on null values – Ygg69 Dec 21 '16 at 13:44
  • what happens when you run this? ALTER TABLE veille_ndd_hist MODIFY tag BIT NOT NULL DEFAULT 0 – Stephen Dec 21 '16 at 13:47
  • When i run this i have this error : Data Truncation error occurred on a write of column 0Data was 0 bytes long and 0 bytes were transferred. – Ygg69 Dec 21 '16 at 13:51
  • try ALTER TABLE veille_ndd_hist MODIFY tag BIT NOT NULL DEFAULT false – Stephen Dec 21 '16 at 13:59
  • I have tried too with false/true, i have the same error :/ – Ygg69 Dec 21 '16 at 14:00
  • please post the result of SHOW CREATE TABLE veille_ndd_hist on your post – Stephen Dec 21 '16 at 14:01
  • i have this : CREATE TABLE `veille_ndd_histo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `veille_ndd` int(11) DEFAULT NULL, `titulaire` longtext COLLATE utf8_unicode_ci, `date_creation_at` datetime DEFAULT NULL, `date_expiration_at` datetime DEFAULT NULL, `tag` bit(1) DEFAULT b'0', PRIMARY KEY (`id`) – Ygg69 Dec 21 '16 at 14:10
  • try this ALTER TABLE veille_ndd_histo MODIFY tag bit(1) NOT NULL DEFAULT b'0 – Stephen Dec 21 '16 at 14:11
  • last kick at it would be the second option: ALTER TABLE veille_ndd_histo CHANGE COLUMN tag tag bit(1) NOT NULL DEFAULT b'0' – Stephen Dec 21 '16 at 14:14
  • I have already the same error :/ : Data Truncation error occurred on a write of column 0Data was 0 bytes long and 0 bytes were transferred. – Ygg69 Dec 21 '16 at 14:23
  • sorry dude. hopefully somebody else can assist you, I'm out of ideas – Stephen Dec 21 '16 at 14:27
  • no problem, thank you for your time and your help :) – Ygg69 Dec 21 '16 at 14:31
  • @Ygg69 i think the problem is because you have some null values on your table. run **update veille_ndd_histo set tag = 0 where tag is null;** then do the alter statement – Stephen Dec 21 '16 at 14:56
  • cool, glad you got it working at end. you may mark the answer – Stephen Dec 21 '16 at 15:04
0

use 

ALTER TABLE veille ALTER COLUMN tag datatype

columns are nullable by default, As long as the column is not declared UNIQUE or NOT NULL, there shouldn't be any problems.

refer to this question: How do I modify a MySQL column to allow NULL?

Community
  • 1
  • 1
VahiD
  • 1,014
  • 1
  • 14
  • 30
0

No need for datatype false Just use the query like below

alter table table_name modify column tag <datatype> not null;
jophab
  • 5,356
  • 14
  • 41
  • 60
  • Thanks but this modify my field 'column default' in my column tag. And i would like modify my field 'IS_NULLABLE' in this same column – Ygg69 Dec 21 '16 at 12:27