Search code examples
sqldatabasecreate-tablecheck-constraints

Create a table for following condition

condition for the problem to be solved:

This is the following condition to create the table

The code i tried to do is

create table article ( 
    ArCode CHAR(5),
    ArName VARCHAR(30) NOT NULL , 
    Rate Number(8,2) , 
    Quantity NUMBER(4) CHECK (Quantity>0) DEFAULT 0 ,
    Class CHAR(1)
);

I couldn't solve the first condition and so i am getting right parenthesis missing for final condition

Solution

  • I would translate your requirement as follows:

    CREATE TABLE article ( 
    ArCode   CHAR(5) PRIMARY KEY CHECK(ArCode like 'A%'), 
    ArName   VARCHAR(30) NOT NULL, 
    Rate     NUMERIC(8,2),
    Quantity NUMERIC(4) DEFAULT 0 CHECK (Quantity >= 0),
    Class    CHAR(1) CHECK(Class in ('A', 'B', 'C'))
);

    Changes to your original code:

    • you want NUMERIC rather than NUMBER

    • ArCode must be declared as PRIMARY KEY, and needs a check constraint to enfore the "Must begin with A" requirement

    • the check constraint on Quantity should allow 0 value (that's the default!)

    • Class needs a check constraint on the list of allowed values