Hi, I was trying to fix some screwed up data by running this by hand:
MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES (154, '2014-11-20 08:36:07.209', 4), (164, '2014-11-24 10:25:06.395', 3), (165, '2014-11-24 10:25:46.987', 3), (171, '2014-11-27 10:35:09.764', 3), (172, '2014-11-27 10:35:41.147', 3), (173, '2014-11-27 11:58:11.06', 3), (174, '2014-11-27 12:00:32.21', 3), (176, '2014-12-01 14:58:41.081', 3), (178, '2014-12-03 09:48:16.415', 3), (179, '2014-12-03 10:07:49.815', 3), (181, '2014-12-05 09:36:35.754', 3), (182, '2014-12-08 08:47:11.349', 3), (183, '2014-12-09 12:48:11.538', 3), (184, '2014-12-09 14:16:26.757', 3), (185, '2014-12-11 14:43:40.76', 3), (186, '2014-12-11 16:23:17.315', 3), (187, '2014-12-12 09:02:29.67', 3), (188, '2014-12-12 12:10:01.006', 3); On a table defined as: CREATE TABLE IF NOT EXISTS SheetOrders ( id IDENTITY PRIMARY KEY, printOrder BIGINT NULL, product BIGINT NULL, sheet BIGINT NOT NULL, amount INT NOT NULL, note VARCHAR(255), status INT NOT NULL, createdAt TIMESTAMP NOT NULL, createdBy BIGINT NOT NULL, FOREIGN KEY (printOrder) REFERENCES PrintOrders(id), FOREIGN KEY (product) REFERENCES Products(id), FOREIGN KEY (sheet) REFERENCES Sheets(id), FOREIGN KEY (createdBy) REFERENCES Users(id) ); What I get is: NULL not allowed for column "SHEET"; SQL statement: MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES (154, '2014-11-20 08:36:07.209', 4), (164, '2014-11-24 10:25:06.395', 3), (165, '2014-11-24 10:25:46.987', 3), ... I don't understand this behavior. I updated several other tables in the same manner - all had NOT NULL and FOREIGN KEY columns and none gave me any errors. I would suppose that when I don't specify the NOT NULL column as NULL explicitly in the merge, the DB should not even care about that column in a MERGE that does not mention that column. What is happening? How do I get around this? I also tried to set the columns NULL with ALTER before the MERGE (then the MERGE worked fine) and then back to NOT NULL which gave me this error afterwards: Column "SHEET" contains null values; SQL statement: ALTER TABLE SheetOrders ALTER COLUMN sheet SET NOT NULL [90081-175] [ERROR:90081, SQLSTATE:90081] But when I queried the table for NULL values like such: SELECT * FROM SheetOrders WHERE sheet = NULL; I got 0 rows back, which means there are no NULL values in that column. So what's the deal? Is the DB corrupt? Is this a bug? Undocumented feature? Why can't I MERGE into the table and set the column to NOT NULL? Regards, David -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
