CODEWITHSUNDEEP
sqloracle-databaseconstraints
user3002209
user3002209

Reputation: 21

constraint check range float

I've got a problem with my sql code (working with oracle).

I'm triyng to make a constraint with body height, which should be between 1,0 and 2,4. The data type is a float. The decimal place is divided with a comma. This ist the code:

alter table tableName
add constraint check_height
check (columnName between 1,0 and 2,4);

I tried to divide the decimal places in the code (I can't change the decimal place holder in the data list) with comma and points, I also tried to change the range and to show the range with < >. Nothing worked so far. Has anyone an idea what I am missing?

The error message is: 'check constraint violated'.

cheers

Upvotes: 2

Views: 3699

Answers (2)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

The decimal place should be separated with a dot . from the fractional part, not a comma, so:

alter table tableName
add constraint check_height
check (columnName between 1.0 and 2.4);

Edit

Regarding comments below - you cannot write a literal number in SQL and PL/SQL using a different decimal point separator than a dot . (documentation). You can alter the session to set the NLS_NUMERIC_CHARACTERS parameter to change the decimal point separator, but it will only be taken into account when converting value of a different type to a number, for example, in the TO_CHAR function.

If you change the NLS_NUMERIC_CHARACTERS, you can use a different separator than a dot . if you enclose the number in quotes (and that will cause an implicit conversion to a number, using the characters set in NLS_NUMERIC_CHARACTERS to determine what is a decimal point, and what is a group separator). So, this will work:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ", ";

ALTER TABLE tableName
ADD CONSTRAINT check_height
CHECK (columnName BETWEEN '1,0' AND '2,4');

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191435

If you already have data in your table with values that would violate the constraint, then you can't create it with the default validate clause:

create table tableName(columnName number);

insert into tablename (columnName) values(2.5);

alter table tableName
add constraint check_height
check (columnName between 1.0 and 2.4);

SQL Error: ORA-02293: cannot validate (STACKOVERFLOW.CHECK_HEIGHT) - check constraint violated
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

(My territory has nls_numeric_characters='.,', so I've used . rather than ,).

You can either correct or remove the invalid values, or allow the bad values to remain while only validating new data by specifying the novalidate clause:

alter table tableName
add constraint check_height
check (columnName between 1.0 and 2.4)
novalidate;

table TABLENAME altered.

New inserts or updates will still be validated, this only affects existing data.

Upvotes: 5

Related Questions