creating a table with null constraint in SQL but have constraint

Question

I want to create a table that column value would be >0 and it can be null.

when I am doing this on the creation of table column

CREATE TABLE Ship
  (
    SITE_ID         INTEGER NOT NULL,
    Ship_CATEGORY  VARCHAR2(50)  CHECK (WRECK_CATEGORY IN ('Military','Government','Passenger', 'Unknown', 'Commercial')),
    Ship_TYPE      VARCHAR2(100) ,
    Ship_TONNAGE   INTEGER CHECK ((WRECK_TONNAGE>0) AND WRECK_TONNAGE IS NULL) ,
    Ship_CAUSE     VARCHAR2(100) NULL CHECK (WRECK_CAUSE IN ('Fire', 'Collision','Unknown',' Deliberate', 'Explosion',' Attack', 'Other')),
    Ship_DATESUNK  DATE NULL,
    Ship_CONDITION VARCHAR2(50) NULL CHECK (WRECK_CONDITION IN ('Excellent', 'Moderate','Poor', 'Very Poor')),
    CONSTRAINT SHIPWRECK_PK PRIMARY KEY (SITE_ID)
  );

in the line "Ship_TONNAGE INTEGER CHECK ((WRECK_TONNAGE>0) AND WRECK_TONNAGE IS NULL) ," when i am inserting null value to this column, it violates the constraint. how i create my table as it satisfy the condition? Thanks

Gordon Linoff · Accepted Answer · 2013-05-26 22:39:11Z

1

Change the check constraint from an and to an or:

CHECK ((WRECK_TONNAGE>0) or WRECK_TONNAGE IS NULL)

Your constraints are referencing the wrong columns, WRECK* instead of SHIP*. Try this definition statement:

CREATE TABLE Ship
  (
    SITE_ID         INTEGER NOT NULL,
    Ship_CATEGORY  VARCHAR2(50)  CHECK (SHIP_CATEGORY IN ('Military','Government','Passenger', 'Unknown', 'Commercial')),
    Ship_TYPE      VARCHAR2(100) ,
    Ship_TONNAGE   INTEGER CHECK ((SHIP_TONNAGE>0) or SHIP_TONNAGE IS NULL) ,
    Ship_CAUSE     VARCHAR2(100) NULL CHECK (SHIP_CAUSE IN ('Fire', 'Collision','Unknown',' Deliberate', 'Explosion',' Attack', 'Other')),
    Ship_DATESUNK  DATE NULL,
    Ship_CONDITION VARCHAR2(50) NULL CHECK (SHIP_CONDITION IN ('Excellent', 'Moderate','Poor', 'Very Poor')),
    CONSTRAINT SHIPWRECK_PK PRIMARY KEY (SITE_ID)
  );

edited May 26, 2013 at 22:39

answered May 26, 2013 at 22:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bolor Ch Over a year ago

Error report: SQL Error: ORA-02290: check constraint (SYS_C00421597) violated 02290. 00000 - "check constraint (%s.%s) violated" *Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint.

Bolor Ch Over a year ago

YES, i EDITED THAT, BUT i STILL HAVING ERRORS THAT VIOLATES THE CONSTRAINT AS ABOVE.

Gordon Linoff Over a year ago

@BolorCh . . . It works for me at SQL Fiddle (sqlfiddle.com/#!4/351d2/13).

Bolor Ch Over a year ago

Now I found my error I created constraint as ' Deliberate' (with space) and then tried to insert value as 'Deliberate' (without space) that is why I had errors. Thanks guys

Collectives™ on Stack Overflow

creating a table with null constraint in SQL but have constraint

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related