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.
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.