MySQL Alter table causes Error: Invalid use of NULL value
My existing table:
+-----------------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date | timestamp | YES | | NULL |
I wanted to alter table like this:
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
But I got this error:
ERROR 1138 (22004) at line 7: Invalid use of NULL value
The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?
mysql
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
add a comment |
My existing table:
+-----------------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date | timestamp | YES | | NULL |
I wanted to alter table like this:
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
But I got this error:
ERROR 1138 (22004) at line 7: Invalid use of NULL value
The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?
mysql
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
add a comment |
My existing table:
+-----------------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date | timestamp | YES | | NULL |
I wanted to alter table like this:
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
But I got this error:
ERROR 1138 (22004) at line 7: Invalid use of NULL value
The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?
mysql
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
My existing table:
+-----------------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date | timestamp | YES | | NULL |
I wanted to alter table like this:
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
But I got this error:
ERROR 1138 (22004) at line 7: Invalid use of NULL value
The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?
mysql
mysql
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
asked Apr 9 '14 at 18:56
Daniel QiuDaniel Qiu
6011817
6011817
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.
Try this
--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;
ALTER TABLE enterprise
MODIFY creation_date TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
add a comment |
You can't use this query until you have NO
NULLvalues in thecreation_date
column.
Update your creation_date column with some default date and then alter the table.
Like this
UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
2
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f22971586%2fmysql-alter-table-causes-error-invalid-use-of-null-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.
Try this
--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;
ALTER TABLE enterprise
MODIFY creation_date TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
add a comment |
It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.
Try this
--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;
ALTER TABLE enterprise
MODIFY creation_date TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
add a comment |
It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.
Try this
--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;
ALTER TABLE enterprise
MODIFY creation_date TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.
Try this
--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;
ALTER TABLE enterprise
MODIFY creation_date TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
answered Apr 9 '14 at 19:00
rs.rs.
20.4k75280
20.4k75280
add a comment |
add a comment |
You can't use this query until you have NO
NULLvalues in thecreation_date
column.
Update your creation_date column with some default date and then alter the table.
Like this
UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
2
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
add a comment |
You can't use this query until you have NO
NULLvalues in thecreation_date
column.
Update your creation_date column with some default date and then alter the table.
Like this
UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
2
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
add a comment |
You can't use this query until you have NO
NULLvalues in thecreation_date
column.
Update your creation_date column with some default date and then alter the table.
Like this
UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
You can't use this query until you have NO
NULLvalues in thecreation_date
column.
Update your creation_date column with some default date and then alter the table.
Like this
UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
edited Nov 14 '18 at 5:05
Joshua Pinter
24.4k8138166
24.4k8138166
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
answered Apr 9 '14 at 18:59
Vignesh Kumar AVignesh Kumar A
18.6k103984
18.6k103984
2
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
add a comment |
2
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
2
2
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
Late comment but I'm guessing that should be either you can't use as long as you have NULL values or until you haven't got any NULL values. Otherwise there wouldn't be an issue.
– thisisboris
Jul 18 '16 at 15:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f22971586%2fmysql-alter-table-causes-error-invalid-use-of-null-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
