Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you want the answer for Oracle or Postgres? Or both? Or MySQL too? Aside from that, using Oracle as an example, you specify the precision and scale of the NUMBER, so it's impossible to match it with a C type which will be constrained by number(s) that're a power of two (-1) - An oracle number will be constrained by a number of digits (eg: 999999). May I ask why you want to do this? Very strange requirement. –  FreshPhilOfSO Aug 21 '12 at 16:12
    
@Phil, updated the question regarding those comments. –  maxschlepzig Aug 21 '12 at 18:16
1  
Ok, I get you. It can't ever be a problem if the same software that SELECTs from the database also does the INSERT. Just something to think about.... –  FreshPhilOfSO Aug 21 '12 at 18:38
1  
Databases actually are way more strict about the data definition size and boundaries than client languages are. RDBMs are also forced to always consider layout changes impact for TBs of on-disk data, a burden that languages are completely free of. This is why usually is the client that models the (more stable and precise) RDBMS type system, not the other way around. –  Remus Rusanu Aug 21 '12 at 20:34
add comment

3 Answers

up vote 3 down vote accepted

A simple restriction of the domain of the SQL data type by a constraint is the most natural way. You have two options

  1. adding a constraint to the column of your table(s) in question
  2. creating a domain for each data type you want to be tailored to fit your given limitations of the programming language

An example of the first options looks like

ALTER TABLE table_name 
  ADD CONSTRAINT constraint_name 
CHECK (column_name > -128 AND column_name < 127);

(change the table, constraint and column name as needed, and adjust the range of the checked bounds)

The second option is a two step process. First add a new domain to your DB (in this example the domain type is named c_byte8) with the command

CREATE DOMAIN c_byte8 NUMERIC CONSTRAINT c_byte8_constraint
 CHECK (VALUE > -128 AND VALUE < 127);

Second create your table with the appropriate types, i.g. c_byte8 instead of NUMERIC as the attribute's type. E.g.

CREATE TABLE test_table_1 (id NUMERIC, byte_value c_byte8);

Please keep in mind that the first solution will require you to add constraints to each attribute you have defined, or you will define in the future. It makes your DB-create script less readable, because of the repetition of the same constraint condition every time you use the restricted domain. A second downside of this approach is, that you can easily miss some columns of your schema when you add those constraints to an existing schema.

An upside of the first solution is that you can extend an existing schema without much hassle.

As for the second approach, the plus is a more readable DB creation script, as is the same with the table definitions of a running production DB, but changing an existing production DB is more complicated.

share|improve this answer
add comment

I'd be tempted to look up the necessary information during the build, and map the result to implementation language's data types. For example, I can look up the information you need from PostgreSQL's information_schema views.

select column_name, data_type, numeric_precision, numeric_precision_radix, numeric_scale
from information_schema.columns
where table_catalog = 'sandbox'
  and table_schema = 'public'
  and table_name = 'payors'
  and column_name = 'payor_id'

column_name  data_type  numeric_precision  numeric_precision_radix  numeric_scale
--
payor_id     integer    32                 2                        0
share|improve this answer
add comment

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:

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.