CODEWITHSUNDEEP
oracle-database
Christopher
Christopher

Reputation: 691

2 and only 2 characters for oracle table

I am trying to create a table in oracle that will accept 2 and only 2 characters. I tried using char(2), but if I put in 1 character in an insert statement, it will accept it. How do I make oracle only accept any inserts of 2 exact characters and reject 1 and 3 and higher characters? I have searched all over the internet and can't seem to find an answer for this.

Thanks! Christopher

Upvotes: 4

Views: 135

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

You can create a CHECK constraint that enforces this restriction

SQL> create table foo (
  2    col1 varchar2(2) NOT NULL
  3   ,check( length(col1) = 2 )
  4  );

Table created.

SQL> insert into foo values( 'ab' );

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into foo values( 'a' )
SQL> /
insert into foo values( 'a' )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0022134) violated

Upvotes: 11

chris
chris

Reputation: 37460

You could use a pre-insert trigger to check the length - I can't think of a way to prevent a single character.

Upvotes: 0

Related Questions