CODEWITHSUNDEEP
sqloracle-databasecheck-constraints
hxx07
hxx07

Reputation: 1

Oracle SQL assign specific values

So I am working on my coursework and I am sort of stuck as to what to do for this one part. The question is this :

Flatpack(FlatpackID, Name, Colour, Type, UnitPrice)

Okay so the one I need help with is the one that is in bold/italic i.e. "Type is one of (Office, Kitchen, Bedroom, General")

How exactly am I declaring this within my 

CREATE TABLE FLATPACK (


);

I asked and I was told it is only allowed those values and nothing else.

Any help would be greatly appreciated! Thanks

Upvotes: 0

Views: 514

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

This is one option (having types restricted by a check constraint):

SQL> CREATE TABLE flatpack
  2  (
  3     flatpackid  NUMBER CONSTRAINT pk_fp PRIMARY KEY,
  4     name        VARCHAR2 (20) NOT NULL,
  5     colour      VARCHAR2 (20),
  6     TYPE        VARCHAR2 (20)
  7                    CONSTRAINT ch_ty CHECK
  8                       (TYPE IN ('Office',
  9                                 'Kitchen',
 10                                 'Bedroom',
 11                                 'General')),
 12     unitprice   NUMBER CONSTRAINT ch_pr CHECK (unitprice BETWEEN 5 AND 500)
 13  );

Table created.

SQL>

Another, better (why? More flexible, as you can add any type you want, without altering the table) option is to create a table which will be referenced by the TYPE column:

SQL> CREATE TABLE flatpack_type (TYPE VARCHAR2 (20) CONSTRAINT pk_ty PRIMARY KEY);

Table created.

SQL> CREATE TABLE flatpack
  2  (
  3     flatpackid  NUMBER CONSTRAINT pk_fp PRIMARY KEY,
  4     name        VARCHAR2 (20) NOT NULL,
  5     colour      VARCHAR2 (20),
  6     TYPE        VARCHAR2 (20)
  7                    CONSTRAINT fk_fp_ty REFERENCES flatpack_type (TYPE),
  8     unitprice   NUMBER CONSTRAINT ch_pr CHECK (unitprice BETWEEN 5 AND 500)
  9  );

Table created.

SQL> insert into flatpack_type                  --> valid values
  2    select 'Office'  from dual union all
  3    select 'Kitchen' from dual union all
  4    select 'Bedroom' from dual union all
  5    select 'Genral'  from dual;

4 rows created.

As of the ID, you could use an identity column (if on 12c or higher), or a standard option for lower versions - a trigger which uses a sequence:

SQL> create sequence seq_fp;

Sequence created.

SQL> create or replace trigger trg_bi_fp
  2    before insert on flatpack
  3    for each row
  4  begin
  5    :new.flatpackid := seq_fp.nextval;
  6  end;
  7  /

Trigger created.

SQL>

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

One method is a check constraint:

constraint chk_flatpack_type check ( Type in ('Office', 'Kitchen', 'Bedroom', 'General') );

Another option is to set up foreign key constraint to a reference table, and have the reference table only contain these values.

Upvotes: 2

Related Questions