[solved] How to make a value "not null" in an existing table lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2024-01-24 11:26 Those good guys, who follow my problems know, that I have problems with my old database. I thought solving them, by enforcing correct inputs controlled by Firebird instead of my code. I have a table where repeatedly unexplained empty values troubled me. So I thought: This is a database, do not allow empty values. And I tried to set to "not null" by FlameRobin. It did not work. I read "Can not get domain info - aborting." How can I do this by an existing table with may data inside? I checked the values by a select request. At the moment there were all NOT NULL. This is the DLL for the table Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE TBKONTRAKTE(  ID_KONTRAKT integer NOT NULL,  NAME_ char(4) NOT NULL,  JAHR_AUS_NAME smallint,  MONATSKENNZAHL smallint,  SCROLLED smallint DEFAULT NULL,  COMM char(2),  ===============> this I want to be NOT NULL  CONSTRAINT PK_TBKONTRAKTE PRIMARY KEY (ID_KONTRAKT),  CONSTRAINT UNQ1_NAME_U_JAHR UNIQUE (NAME_,JAHR_AUS_NAME)); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TBKONTRAKTE TO  SYSDBA WITH GRANT OPTION GRANTED BY SYSDBA; ---------------------------------------------------------------------- Zvoni · 2024-01-24 11:35 Are you sure it's the Field "COMM"? I see "JAHR_AUS_NAME" being part of a UNIQUE-Constraint, but JAHR_AUS_NAME could take NULL-Values (theoretically) QuoteHow can I do this by an existing table with may data inside?You have to get rid of the NULLs in that Column as a FIRST step, THEN you ALTER the Table and Column 1) Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i UPDATE tbkontrakteSET COMM='  ' --these are two spaces 2) Untested (Aircode) Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE tbkontrakteALTER COLUMN COMM CHAR(2) NOT NULL DEFAULT 'xx' --Default Value is optional ---------------------------------------------------------------------- rvk · 2024-01-24 11:43 Quote from: Nicole on January 24, 2024, 12:26:14 pmI have a table where repeatedly unexplained empty values troubled me. So I thought: This is a database, do not allow empty values. And I tried to set to "not null" by FlameRobin. It did not work. I read "Can not get domain info - aborting." How can I do this by an existing table with may data inside?What field are you trying to set to NOT NULL...? And what command does FlameRobin want to execute when you do that? Quote from: Nicole on January 24, 2024, 12:26:14 pmI checked the values by a select request. At the moment there were all NOT NULL.With what SELECT did you check that? ---------------------------------------------------------------------- Zvoni · 2024-01-24 11:44 Quote from: rvk on January 24, 2024, 12:43:17 pmWhat field are you trying to set to NOT NULL...? Look at her Line 8 in her SQL above ---------------------------------------------------------------------- rvk · 2024-01-24 11:46 Quote from: Zvoni on January 24, 2024, 12:44:58 pmQuote from: rvk on January 24, 2024, 12:43:17 pmWhat field are you trying to set to NOT NULL...? Look at her Line 8 in her SQL above Ha, Ok. I'm puzzled by the "Can not get domain info - aborting." from FlameRobin so I also wanted to know the command that it wants to execute. When there are NULL values I would expect a different error. ---------------------------------------------------------------------- Thaddy · 2024-01-24 11:52 So much to the opinion that I always flame Robin... Umptiest example. ---------------------------------------------------------------------- Nicole · 2024-01-24 12:07 Yes, I am sure I want to set the line 8 to not null Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   COMM char(2),  ===============> this I want to be NOT NULL There are no NULL value at the moment. Because - sobb - NULL is a thing "which cannot happen". There are several checks in my code, that it never happens. However: it happens. ;-(( At the moment I have no idea, why. But I hope the database-exception will show me. Back to my question: How can I set it to "not null"? and when I do this, perhaps it is a good idea to set it to "no blanc signs" as well. With or without Flamerobin? Firebird Editor Pro would be available as well. Or of cource - Lazarus. ---------------------------------------------------------------------- rvk · 2024-01-24 12:12 Quote from: Nicole on January 24, 2024, 01:07:32 pmThere are several checks in my code, that it never happens. However: it happens. ;-(( At the moment I have no idea, why. But I hope the database-exception will show me.It won't. It will just tell you that the constraint can't be created because of A record. Not which record. Quote from: Nicole on January 24, 2024, 01:07:32 pmBack to my question: How can I set it to "not null"? and when I do this, perhaps it is a good idea to set it to "no blanc signs" as well. With or without Flamerobin?You'll need to do it with execute SQL. It seems that FlameRobin has some limitations/bugs there (it wants to get the domain which it can't). You would need to execute the SQL itself as Zvoni already showed you. ---------------------------------------------------------------------- Zvoni · 2024-01-24 12:41 btw: Beware WHAT you use as a "Default" for COMM (i used two spaces in the UPDATE, and 'xx' in the ALTER TABLE) IIRC, COMM is Column you reference from another Table as a ForeignKey there EDIT: Just read up on Firebird and "Domain" If i understand it correctly, a Domain in Firebird is a User-Defined type for a Column. Kind of like in Pascal Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Type   TSomeArray = Array[0..9] Of String; Var  TSA:TSomeArray; So, if FB complains about a Domain not found, it sounds like TS might use Datatypes in her CREATE TABLE (or whatever command), that are NOT native Datatypes, but Firebird interprets them as NOT-Native so it must be Custom, so there must be a Domain-Definition Maybe a Typo? Or usage of Datatypes, that FB doesn't understand? btw: Is that backup/Restore thing from the same Firebird-Version? Say, in FB2.5 there was a Datatype, which was removed in FB3/4, and her Database is 2.5 which uses this Datatype, and the Restore to FB3/4 fails, because the Datatype is not supported anymore, or whatever. Just thinking out loud here.... ---------------------------------------------------------------------- rvk · 2024-01-24 12:54 No, it just seems like FlameRobin always needs something of a domain when doing NOT NULL dialog. The dialog is attached. But CHAR(2) doesn't have anything to do with domain. So maybe that's the reason for the error. But even when adding domain info it still throws that error. Using just SQL might be a better idea. ---------------------------------------------------------------------- Nicole · 2024-01-24 15:16 @Zvoni Quotebtw: Beware WHAT you use as a "Default" for COMM (i used two spaces in the UPDATE, and 'xx' in the ALTER TABLE) What do you mean by "two spaces"? Do I have this anywhere? If yes, this would be a disaster, worse than NULL. (you do not want to know the details) About Firebird version: The DB is about 10 years old, may be older. It may even be Firebird 1 generated. And old IBExpress from Russia did it for me. The version I had once was Firebird 2.5. I migrated 2023 to Firebird 4. For this I made a backup and restored it into a FB 4 db. And indeed: I have a domain datatype, because I thought I would need this. Only one, I wish I had never used it (but I was young...). Here it is "CREATE DOMAIN "Laufnummer_small" AS smallint; I wish, I can get anyhow rid of it. But I bet the attempt would result into a disaster? My low knowledge about databases will give it the rest ;-) Would you recommend to attempt to delete the domain? If used, it should not work. @ rvk indeed, I cannot see this strange domain in the table-properties at first sight. Hope, this is true. And would you be so kind to tell me the correct SQL-statement for NOT NULL and safe me a hour of finding out it by myself? I try it, but I bet the syntax is wrong: Alter table tbkontrakte set COMM NOT NULL; ---------------------------------------------------------------------- alpine · 2024-01-24 16:12 @Nicole QuoteAnd I tried to set to "not null" by FlameRobin. It did not work. I read "Can not get domain info - aborting."Most probably the error is unrelated to your try to "un-NUL" the column COMM. There is no such value as NOT NULL. Only a postfix logical operations: x IS NULL and x IS NOT NULL. QuoteI try it, but I bet the syntax is wrong: Alter table tbkontrakte set COMM NOT NULL;Sure you can bet. Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE TBKONTRAKTE ADD CONSTRAINT COMM_CHECK CHECK(COMM IS NOT NULL); Edit: Forgot to put the name for the constraint. ---------------------------------------------------------------------- rvk · 2024-01-24 16:26 Quote from: Nicole on January 24, 2024, 04:16:03 pmAnd would you be so kind to tell me the correct SQL-statement for NOT NULL and safe me a hour of finding out it by myself? I try it, but I bet the syntax is wrong: On the Firebird site I found this faq. https://www.firebirdfaq.org/faq103/ So, officially you would need to create a temp column, copy the data, dropping the original column and renaming the temp to the original. Otherwise you can change the RDB$RELATION table (as seen in the faq) but that doesn't check for existing constraints so you need to make sure they are correct. The ADD CONSTRAINT with CHECK() (from above post) is possible but is not exactly the same as NOT NULL. It does not set the RDB$NULL_FLAG field (which NOT NULL does). But it might do the job for you. ---------------------------------------------------------------------- alpine · 2024-01-24 16:51 Quote from: rvk on January 24, 2024, 05:26:44 pmThe ADD CONSTRAINT with CHECK() (from above post) is possible but is not exactly the same as NOT NULL. It does not set the RDB$NULL_FLAG field (which NOT NULL does). But it might do the job for you. At least it can easily be DROPped out. IMO not sure the OP has a clear idea what he is doing, maybe he must share what is the exact trouble: QuoteI have a table where repeatedly unexplained empty values troubled me.Why? (Example - it should be something from the business logic) ---------------------------------------------------------------------- Nicole · 2024-01-24 17:04 Thank you so much all of you! I could add the not NULL and as suddenly there opened an editor on doing so, - I added the not blank as well. So it should work. Why it is that bad, when I have no value there? Because a select statement needs it for one of the most important result. If the value is blank, the data behind are not found. It connects them by a join-query. Why I prefer to have an exception by the database? Because this would allow me to find where in my code may be the point, which is responsible for the blank. At the moment I find out after three or even 6 months: yes, it happened again in one case of 113 a day. The exception will give me a chance to find out, in which unit or even in which software it happens. My program connects to other software.