Add resuable values to database libreoffice base?

[rokejulianlockhart]

I want reusable “objects” of sorts that I can use to enter in tables in order to not have to do find-and-replace operations on unnecessarily duplicate data – variables, basically.

Additionally, like the first part of

Default Values - Database

explains, I’d like to be able to restrict entry in certain fields to certain variables, or “default values”, as it calls them.

Restrict data input by using validation rules - Microsoft Support

appears to demonstrate that Access supports this.

Is this possible, and if so, how can I do so?

I know that Memento Database provided this somewhat, but it was inconsistently implemented. This was the reason that I wanted to use a database rather than a spreadsheet for storing data.

[Villeroy]

menu:Tools>SQL…

ALTER TABLE "table name" ALTER COLUMN "column name" SET DEFAULT 1

menu:View>Refresh Tables

[Wanderer]

@rokejulianlockhart

to be able to restrict entry in certain fields to certain variables, or “default values”,

… but a default value is no restriction. You can still give any value fitting a datatype, a default only fills the gap, if no value is entered, like putting current date, if I don’t write a date.

[Villeroy]

menu:Tools>SQL…

ALTER TABLE "table name" ADD CONSTRAINT "Percent_Range" CHECK "column name" BETWEEN 1 AND 100

menu:View>Refresh Tables

[rokejulianlockhart]

@Wanderer

… but a default value is no restriction. You can still give any value fitting a datatype, a default only fills the gap, if no value is entered, like putting current date, if I don’t write a date.

I don’t understand that sentence. Could you rephrase it for me?

[rokejulianlockhart]

@Villeroy

ALTER TABLE "table name" ALTER COLUMN "column name" SET DEFAULT 1

What does that do?

[Wanderer]

@rokejulianlockhart

don’t understand that sentence. Could you rephrase it for me?

No, as I actually have no idea, what you really want to do. But it seems Villeroy did understand, as his second comment sets a restriction.
.
As you asked for his first comment:

@rokejulianlockhart

@Villeroy

> ALTER TABLE "table name" ALTER COLUMN "column name" SET DEFAULT 1
> ```

What does that do?

The line would set a default value if 1 to one column of a table. You have to provide the real names…

[Villeroy]

When you store a new record to table “table name” with no value in numeric column “column name” the new record will be stored with value 1 in “column name”.

[iplaw67]

That link you provided appears to describe what are known as “Constraints” in SQL jargon.

You have been provided with a sample Sql statement that defines a constraint for a given database field.

Constraints can be used to set minimum values, ranges of valid values (as in the example given you), maximum values, etc, for a field in the table, depending on the version of Sql supported by the database engine you are using.

For example, for embedded hsqldb 1.8:

https://hsqldb.org/doc/1.8/guide/ch09.html#alter_table-section

[Villeroy]

@iplaw67

That link you provided appears to describe what are known as “Constraints” in SQL jargon.

However, the first link describes “default values” as vague as possible whereas Microsoft descibes SQL constraints without mentioning “SQL” nor “constraint”, which is a major issue why I can’t stand MS software. They use their very own special terminology. In German documentation they use their very own special German terminology which is just creepy.

[rokejulianlockhart]

That answers enough the second question. Thanks. I think I’ll re-ask the first one differently when I can describe it somewhat better.