I have the following table:

mysql> describe LEVEL1_CUSTOMER;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| CUSTOMER_NUM  | char(3)      | NO   | PRI | NULL    |       |
| CUSTOMER_NAME | char(50)     | YES  |     | NULL    |       |
| BALANCE       | decimal(8,2) | YES  |     | NULL    |       |
| CREDIT_LIMIT  | decimal(8,2) | YES  |     | NULL    |       |
| REP_NUM       | char(2)      | YES  |     | NULL    |       |
| CUSTOMER_TYPE | char(1)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

I want to change CREDIT_LIMIT to not accept NULLS, I tried the following command but it gives me an ambiguous error. am I doing the syntax right, I got that command from a mysql book so I assume it should work.

mysql> alter table LEVEL1_CUSTOMER
    -> modify CREDIT_LIMIT not null;
ERROR 1064 (42000): 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 'not null' at line 2
Re: Alter Table and NOT NULL 80 80
Member Avatar

The complete column specification should be applied:

mysql> alter table LEVEL1_CUSTOMER modify CREDIT_LIMIT DECIMAL(8,2) NOT NULL;

You should notice that in standard sql you can't change from NULL to NOT NULL if there are already rows having NULL values. You can count the rows containing NULL values:

select count(*) from LEVEL1_CUSTOMER where CREDIT_LIMIT IS NULL
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.