CODEWITHSUNDEEP
sql
Anton Lahti
Anton Lahti

Reputation: 472

Is there a portable way to write a CHECK constraint that checks if a TEXT value is an integer?

Is there a portable way to write the CHECK below?

CREATE TABLE tbl (
    id1 INTEGER NOT NULL,
    id2 TEXT NOT NULL,

    PRIMARY KEY (id1),
    CHECK ('check that id2 is positive integer')
)

Upvotes: 0

Views: 101

Answers (3)

Syed Ahmad
Syed Ahmad

Reputation: 82

Use the data type of id2 integer and use constraint as below

CREATE TABLE tbl (id1 integer NOT NULL PRIMARY KEY, 
                  id2 integer NOT NULL CHECK (id2 > 0));

Upvotes: 0

The Impaler
The Impaler

Reputation: 48850

No, there is no portable way of dealing with malformed data.

Having said that, each database has its own ways of dealing with malformed data. The typical solution is to use a regular expression in a check constraint. Other solution can try to cast the value to a numeric scalar. All these solutions work, but they have their quirks on each database.

I would suggest you fix the data type if possible. That will greatly simplify all these headaches.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37477

Use the right datatype, integer, not text, and check for the values to be positive (or non negative).

CREATE TABLE tbl
             (id1 integer
                  NOT NULL,
              id2 integer
                  NOT NULL,
              PRIMARY KEY (id1),
              CHECK (id2 > 0));

Upvotes: 0

Related Questions