[Solved] SQL to make entry required

[Nocton]

Is there an SQL command I can use to make 'Entry Required' for a field?
I have a split database so cannot make the change via the table design.

[FJCC]

A command of the form

Code: Select all

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET [NOT] NULL

entered through the menu Tools -> SQL should work. The above example is from the documentation for the HSQLDB 1.8 database, which is what ships with OpenOffice. The square brackets around the NOT just mean it can be excluded if you want to set the column to allow NULL. In your command, you would include the NOT with no brackets.

[Nocton]

Many thanks, FJCC.

[Villeroy]

Alter the whole column definition:
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" INTEGER DEFAULT 0 NOT NULL; [fails if existing data are incompatible with integer type]

Alter only the nullability:
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET NOT NULL; [fails if existing data contain Null values]

Remove default value:
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET DEFAULT NULL;

Some other aspects:
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET DEFAULT 43;
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET PRIMARY KEY; [fails if existing data are not unique]
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET GENERATED BY DEFAULT AS IDENTITY; [auto PK for int and bigint types]
ALTER TABLE "Table Name" ADD FOREIGN KEY ("Column1", "Column2") REFERENCES "other table" ("ColumnA", "ColumnB"); [relation between one, two or more columns in (braces).fails if types don't match, if there are data that do not exist in the referened table]
ALTER TABLE "Table Name" ADD CONSTRAINT "D1geqD2" CHECK "Date1">="Date2"; [not possible in the GUI anyway]