How to set a maximum size for a BLOB field?

Question

I want to insert images and I want each record to have at most 100M, if this value is exceeded should be returned an error in the database itself.

For example illustration of the problem: (in the same way that error is returned if we try to insert a 35-character string in a varchar(30) column, it must be done with the larger image of 100M).

So if the image sent is larger than 100M the database should report the error and reject the record, I will use the following table as an example and the respective error generated:

CREATE TABLE imagem  
(  
  ID       NUMBER(5)  NOT NULL,  
  NOME     VARCHAR2(50),  
  IMG      BLOB(100M)  
)

Error at line 1 ORA-00907: Missing right parenthesis

Answer 1

To solve your problem, we can put a constraint check, and enforce domain integrity by limiting the values that are accepted by a column.

They are similar to FOREIGN KEY as they control the values that are placed in a column. The difference is how to determine which values are valid, constraint determine valid values from a logical expression that is not based on data from another column.

In your case it can be solved with the expression:

create table imagem
(
  id   number(5),
  nome varchar2(50),
  img  clob constraint CK_IMGMAX_100 check(length(img) < 100)
);

I mean, we created a constraint for the image column called, CK_IMGMAX_100 and checking that the image size is less than 100, so column only allows adding values less than 100 Mb.

Another interesting case to use, in a more usual example, is to check the size of the person’s name, example:

ALTER TABLE imagem
ADD CONSTRAINT CK_IMAGEM
CHECK ( length(nome) BETWEEN 3 AND 50 );

So the field nome only received values between 5 and 50 characters.