Sample Oracle DB for Real Estate Agency

Question

Im trying to build a sample database for a project about a real estate agency,on the table about the realties i have a column Realtie_id and i want it to start with 11%%% what type should it be (int or varchar ) and how do I make the constraint ?

 create table Realties (
    rid int not null,
    address varchar(50),
    m2 real not null,
    r_type varchar(20),
    primary key (rid),
    constraint c_rid check(rid in (.....
    );  

Answer 1

It depends on what you'll be storing in there.

• if it is a string, use VARCHAR2
• if it is a number, use NUMBER (or INT)

Constraint in any case might be

SQL> create table realties
  2    (rid int constraint ch_rid check (substr(to_char(rid), 1, 2) = '11'));

Table created.

SQL> insert into realties (rid) values ('abc');
insert into realties (rid) values ('abc')
                                   *
ERROR at line 1:
ORA-01722: invalid number


SQL> insert into realties (rid) values ('245');
insert into realties (rid) values ('245')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_RID) violated


SQL> insert into realties (rid) values ('1245');
insert into realties (rid) values ('1245')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_RID) violated


SQL> insert into realties (rid) values ('11245');

1 row created.

SQL>