snippetMinor
How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?
Viewed 0 times
int64_tint32_tuint64_tlikesequencetypehowrestrict
Problem
In C99 I can be pretty clear about the size/domain of an integer type.
When interfacing a SQL database like Oracle or Postgresql from a C program I want to create a table with integer types that match the client ones.
For example to match int64_t, uint64_t or uint32_t etc. for a sequence like artificial primary key column.
Just using NUMBER would allow for data that yields an over-/underflow or are not representable in the client language, right?
How to correctly define such a column?
Motivation
Why do you want to such a thing? Because you want to write reliable software. Say you design a database client application and you assume that you only ever need less than 2^32 IDs. Thus, to save space (e.g. with client side arrays etc.) you use uint32_t (or just int). But to be safe the database should enforce that restriction such that your system fails in a well-defined, gracious and noticeable way (e.g. rejecting violating inserts) in case 20 years later some user want to insert tons of IDs or something like that.
Database system
Ideally, the solution should be portable between databases (after all, SQL is an international standard) - but an answer which is Oracle/Postgres specific would be instructive as well - often if you know how to do it on one database system, you can lookup the abstract concept in the documentation of the other one.
Types vs. triggers
It does not have to be a type-only solution - triggers are other mechanism that enforce constraints are fine as well. I just imagined using types could be a convenient way.
When interfacing a SQL database like Oracle or Postgresql from a C program I want to create a table with integer types that match the client ones.
For example to match int64_t, uint64_t or uint32_t etc. for a sequence like artificial primary key column.
Just using NUMBER would allow for data that yields an over-/underflow or are not representable in the client language, right?
How to correctly define such a column?
Motivation
Why do you want to such a thing? Because you want to write reliable software. Say you design a database client application and you assume that you only ever need less than 2^32 IDs. Thus, to save space (e.g. with client side arrays etc.) you use uint32_t (or just int). But to be safe the database should enforce that restriction such that your system fails in a well-defined, gracious and noticeable way (e.g. rejecting violating inserts) in case 20 years later some user want to insert tons of IDs or something like that.
Database system
Ideally, the solution should be portable between databases (after all, SQL is an international standard) - but an answer which is Oracle/Postgres specific would be instructive as well - often if you know how to do it on one database system, you can lookup the abstract concept in the documentation of the other one.
Types vs. triggers
It does not have to be a type-only solution - triggers are other mechanism that enforce constraints are fine as well. I just imagined using types could be a convenient way.
Solution
I don't think you can restrict this by the choice of the datatype.
However you can create a check constraint that ensures that no value is inserted that could not be represented in your programming language:
You will have to replace the actual value with the one that matches the chosen datatype (I don't do C, so I don't know what those limits are).
However you can create a check constraint that ensures that no value is inserted that could not be represented in your programming language:
alter table foo
add constraint check_type_range check (int_column < 32767);You will have to replace the actual value with the one that matches the chosen datatype (I don't do C, so I don't know what those limits are).
Code Snippets
alter table foo
add constraint check_type_range check (int_column < 32767);Context
StackExchange Database Administrators Q#22844, answer score: 3
Revisions (0)
No revisions yet.