Relational Databases Check Constraint ORACLE

Question

I am working on a project for Uni and I don't have much knowledge or experience with databases. I am trying to create a database in Oracle with a table that contains manufactured parts that can be of 2 types, say 1 and 2. When the part is of type 1 I will store in the table its location, when it's of type 2 I will store in the same table the lead time. Thus I will have null values for the other column in both cases (I am aware of the issues with the null values, but after thinking about it and researching what is the best way of dealing with this, I decided to do it like this, as I have only a small amount of atributes). My problem is in the CHECK CONSTRAINT. I tried to do it this way:

CREATE TABLE manufactured (
PID INT NOT NULL,
PARTTYPE NUMBER (1) NOT NULL,
CHECK (PARTTYPE IN (1,2)),
CONSTRAINT REFMAN FOREIGN KEY (PID, PARTTYPE) REFERENCES PART (PID, PARTTYPE),
LOCATION VARCHAR (50),
CONSTRAINT LOC CHECK (PARTTYPE=1 AND LOCATION IS NOT NULL),
CONSTRAINT LOC2 CHECK(PARTTYPE=2 AND LOCATION IS NULL),
LEAD_TIME VARCHAR (50),
CONSTRAINT LEADTIME CHECK (PARTTYPE=2 AND LEAD_TIME IS NOT NULL),
CONSTRAINT LEADTIME2 CHECK (PARTTYPE=1 AND LEAD_TIME IS NULL),
CONSTRAINT PK_MAN PRIMARY KEY (PID));

This is not working.

I tried to insert a record as follows:

insert into manufactured(PID, PARTTYPE, LOCATION) values(101,1,'Warehouse1');

And I get the error: ORA-02290: check constraint (*****.LEADTIME) violated

I also tried:

insert into manufactured values (101,1,'Warehouse1');

And I get the error:

ORA-00947: not enough values

And finally with this:

insert into manufactured(PID, PARTTYPE, LEAD_TIME) VALUES (102, 2, '2 WEEKS');

I get the following error: ORA-02290: check constraint (****.LEADTIME2) violated

Thank you in advance for your help.

What does "not working" mean? For code questions give a [mcve] PS A constraint fails when it evaluates to false, otherwise it is satisfied. — philipxy, Feb 27 '19 at 21:27
That helps but it's still not a [mcve]. Eg you don't say what you think this does, you just give wrong code. Eg you don't give cut & paste & runnable code. Eg your code isn't minimal--it has lots of errors, you didnt' start with smallest code that works that you added a smallest error to. PS When you get an error message search the manual & the web & check your code for all cases it covers. — philipxy, Feb 27 '19 at 23:37
Re design options for subtyping: [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) (etc) — philipxy, Feb 28 '19 at 00:55

Matthew McPeak · Accepted Answer · 2019-02-28T14:37:11.210

This insert statement:

insert into manufactured(PID, PARTTYPE, LOCATION) values(101,1,'Warehouse1');

...fails because your LEADTIME constraint requires that PARTTYPE=2. (It's an AND condition, so if PARTTYPE=1 the constraint will fail regardless of the value for LEAD_TIME.)

This is what I think you are looking for:

CREATE TABLE manufactured (
PID INT NOT NULL,
PARTTYPE NUMBER (1) NOT NULL,
CHECK (PARTTYPE IN (1,2)),
CONSTRAINT REFMAN FOREIGN KEY (PID, PARTTYPE) REFERENCES PART (PID, PARTTYPE),
LOCATION VARCHAR (50),
--CONSTRAINT LOC CHECK (PARTTYPE=1 AND LOCATION IS NOT NULL),
--CONSTRAINT LOC2 CHECK(PARTTYPE=2 AND LOCATION IS NULL),
CONSTRAINT LOC CHECK (PARTTYPE=1 AND LOCATION IS NOT NULL OR PARTTYPE=2 AND LOCATION IS NULL),
LEAD_TIME VARCHAR (50),
--CONSTRAINT LEADTIME CHECK (PARTTYPE=2 AND LEAD_TIME IS NOT NULL),
--CONSTRAINT LEADTIME2 CHECK (PARTTYPE=1 AND LEAD_TIME IS NULL),
CONSTRAINT LEADTIME CHECK (PARTTYPE=1 AND LEAD_TIME IS NULL OR PARTTYPE=2 AND LEAD_TIME IS NOT NULL),
CONSTRAINT PK_MAN PRIMARY KEY (PID));

Basically, make one constraint on each column that enforces the whole set of logic for that column.

If you really want two constraints on each column, you can do that too. If so, post a comment and I'll update this answer. I don't want to clutter/confuse the issue otherwise.

I don't need to do it with 2 constraints for column, I just didn't know how to do it. But this was exactly what I needed! Thank you so much! — Rach, Feb 27 '19 at 22:33
@Mary - when you receive a helpful answer please upvote it and/or accept it as if it's the best answer. Upvotes and acceptances increase the value of this site for future Seekers. — APC, Feb 28 '19 at 07:57

score 0 · Answer 2 · answered Feb 27 '19 at 21:20

I don't know which RDBMS you use. For example in Oracle CHECK constraint accepts nulls.

As i see there are different attributes/datatypes for each party type. There are two approach:

split data into two separate tables. In this solution some triggers may be needed.
- tab1: manufactured_1 (attributes+constraints for PID 1)
- tab2: manufactured_2 (attributes+constraints for PID 2)
use "after insert/update" trigger - it'll set unnecessary data to null. For example, if in the table will be time for PID = 1 than trigger will set time value to null.

Barbaros Özhan · Answer 3 · 2019-02-27T22:30:18.483

The Error ORA-00947: not enough values for

insert into manufactured values (101,1,'Warehouse1'); is obvious,

since the last column (lead_time) of the table(manufactured) is missing for the values list.
The Errors ORA-02290: check constraint stem from the dependent conditions among the check constraints LEADTIME and LEADTIME2, those should be combined as
CONSTRAINT LEADTIME CHECK ((PARTTYPE=2 AND LEAD_TIME IS NOT NULL) OR (PARTTYPE=1 AND LEAD_TIME IS NULL)).
The same logic works also for constraints LOC and LOC2 which should yield

CONSTRAINT LOC CHECK ((PARTTYPE=1 AND LOCATION IS NOT NULL) OR (PARTTYPE=2 AND LOCATION IS NULL))

Relational Databases Check Constraint ORACLE

3 Answers3