1

I have created my tables as shown below (I am using Oracle XE). The problem that occurs is that when I try to insert values into the tables, I get an error because the value of one's foreign key, has not been entered as a primary key of the other. For example, see tables DEPARTMENT and EMPLOYEE. Below, along with table creation code, is also the data inserting part. How to bypass that problem? 

CREATE TABLE DEPARTMENT
(
DNAME       VARCHAR(10),
DEPTID      INTEGER     NOT NULL,
MNG_IDNO    INTEGER     NOT NULL,
PRIMARY KEY (DEPTID)
);

CREATE TABLE EMPLOYEE
(
IDNO        INTEGER         NOT NULL,
FNAME       VARCHAR(10)     NOT NULL,
LNAME       VARCHAR(10)     NOT NULL,
GENDER      VARCHAR(6)      NOT NULL,
CITY        VARCHAR(20),
AREA        VARCHAR(20),
STREET      VARCHAR(50),
HOUSENO     VARCHAR(4),
ZIPCODE     INTEGER,
BIRTHDAY    DATE,
SALARY      INTEGER,
IBAN        VARCHAR(34),
DEPT_ID     INTEGER         NOT NULL,       
PRIMARY KEY (IDNO)
);

CREATE TABLE CUSTOMER
(
IDNO        INTEGER         NOT NULL,
FNAME       VARCHAR(10)     NOT NULL,
LNAME       VARCHAR(10)     NOT NULL,
GENDER      VARCHAR(6)      NOT NULL,
CITY        VARCHAR(20),
AREA        VARCHAR(20),
STREET      VARCHAR(50),
HOUSENO     VARCHAR(4),
ZIPCODE     INTEGER,
BIRTHDAY    DATE,
PRIMARY KEY (IDNO)
);

CREATE TABLE CAR
(
VIN         INTEGER         NOT NULL,
BRAND       VARCHAR(10)     NOT NULL,
MODEL       VARCHAR(10)     NOT NULL,
COLOR       VARCHAR(10)     NOT NULL,
TRANTYPE    VARCHAR(10)     NOT NULL,
FUELTYPE    VARCHAR(10)     NOT NULL,
ENGCC       INTEGER         NOT NULL,
ENTRYDATE   DATE,
PRICE       INTEGER         NOT NULL,
PAYMENTMETHOD       VARCHAR(10)     NOT NULL,
DEPT_ID     INTEGER         NOT NULL,
CUST_IDNO   INTEGER         NOT NULL,
PRIMARY KEY (VIN)
);

CREATE TABLE CAR_PARTS
(
PARTNUM     VARCHAR(25)     NOT NULL,
PARTNAME    VARCHAR(25)     NOT NULL,
COST        INTEGER         NOT NULL,
PRIMARY KEY (PARTNUM)
);

CREATE TABLE USED_CAR
(
CAR_VIN     INTEGER         NOT NULL,
MILEAGE     INTEGER         NOT NULL,
REGDATE     DATE            NOT NULL,
PRIMARY KEY (CAR_VIN)
);

CREATE TABLE SERVICE
(
DUE_DATE        DATE,
ARR_DATE        DATE,
COST            INTEGER         NOT NULL,
SERVICE_NUM     INTEGER         NOT NULL,
CUST_IDNO       INTEGER         NOT NULL,
PAYMENTMETHOD   VARCHAR(10)     NOT NULL,
CAR_VIN         INTEGER         NOT NULL,
PRIMARY KEY (SERVICE_NUM)
);

CREATE TABLE CUST_PHONUMBER
(
PHONE_NUM       INTEGER         NOT NULL,
CUST_IDNO       INTEGER         NOT NULL,
PHONE_TYPE      VARCHAR(10),
PRIMARY KEY (CUST_IDNO, PHONE_NUM)
);

CREATE TABLE CAR_DEFECTS
(
DEFECTS         VARCHAR(100)    NOT NULL,
CAR_VIN         VARCHAR(100)    NOT NULL,
PRIMARY KEY (DEFECTS, CAR_VIN)
);

CREATE TABLE EMPLOYEE_PHONUMBER
(
PHONENUM        INTEGER         NOT NULL,
EMPL_IDNO       INTEGER         NOT NULL,
PRIMARY KEY (PHONENUM, EMPL_IDNO)
);

CREATE TABLE SERVICE_DETAILS
(
SERVICENUM      INTEGER         NOT NULL,
PROBLEMS        VARCHAR(100)    NOT NULL,
SOLUTION        VARCHAR(100),
PRIMARY KEY (PROBLEMS, SERVICENUM)
);

CREATE TABLE HAS
(
PART_NUM        VARCHAR(25)     NOT NULL,
CAR_VIN         INTEGER         NOT NULL,
PRIMARY KEY (PART_NUM, CAR_VIN)
);

CREATE TABLE USES
(
PART_NUM        VARCHAR(25)     NOT NULL,
SERVICE_NUM     INTEGER         NOT NULL,
PRIMARY KEY (PART_NUM, SERVICE_NUM)
);


ALTER TABLE DEPARTMENT          ADD FOREIGN KEY (MNG_IDNO) REFERENCES EMPLOYEE(IDNO);
ALTER TABLE EMPLOYEE            ADD FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPTID);
ALTER TABLE CAR                 ADD FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPTID);
ALTER TABLE CAR                 ADD FOREIGN KEY (CUST_IDNO) REFERENCES CUSTOMER(IDNO);
ALTER TABLE SERVICE             ADD FOREIGN KEY (CUST_IDNO) REFERENCES CUSTOMER(IDNO);
ALTER TABLE SERVICE             ADD FOREIGN KEY (CAR_VIN)   REFERENCES CAR(VIN);
ALTER TABLE USED_CAR            ADD FOREIGN KEY (CAR_VIN) REFERENCES CAR(VIN);
ALTER TABLE HAS                 ADD FOREIGN KEY (PART_NUM) REFERENCES CAR_PARTS(PARTNUM);
ALTER TABLE HAS                 ADD FOREIGN KEY (CAR_VIN) REFERENCES CAR(VIN);
ALTER TABLE USES                ADD FOREIGN KEY (PART_NUM) REFERENCES CAR_PARTS(PARTNUM);
ALTER TABLE USES                ADD FOREIGN KEY (SERVICE_NUM) REFERENCES SERVICE(SERVICE_NUM);
ALTER TABLE CUSTOMER_PHONUMBER  ADD FOREIGN KEY (CUST_IDNO) REFERENCES CUSTOMER(IDNO);
ALTER TABLE EMPLOYEE_PHONUMBER  ADD FOREIGN KEY (EMPL_IDNO) REFERENCES EMPLOYEE(IDNO);
ALTER TABLE SERVICE_DETAILS     ADD FOREIGN KEY (SERVICENUM) REFERENCES SERVICE(SERVICE_NUM);

Data Insert:

INSERT INTO EMPLOYEE VALUES (1234,'ANTREAS','GEORGIOU','MALE','NICOSIA','STROVOLOS','PANAYIAS','12',2682,TO_DATE('01.01.1989', 'DD.MM.YYYY'),5000,9999999,1);
INSERT INTO EMPLOYEE VALUES (1235,'KOSTAS', 'KOSTA', 'MALE', 'NICOSIA','STROVOLOS','VIZANTIOU','12',2064,TO_DATE('01.02.1980', 'DD.MM.YYYY'),5000,9999998,2);
INSERT INTO EMPLOYEE VALUES (1236,'MARIA', 'ANTREOU', 'FEMALE', 'NICOSIA','EGKOMI','TSAROU','12',2522,TO_DATE('05.05.1988', 'DD.MM.YYYY'),5000,9999995,3);
INSERT INTO EMPLOYEE VALUES (1237,'GEORGIA', 'ANTREOU', 'FEMALE', 'NICOSIA','EGKOMI','TSAROU','13',2522,TO_DATE('05.05.1978', 'DD.MM.YYYY'),5000,9999996,4);

INSERT INTO DEPARTMENT VALUES ('SERVICE',1,1234);
INSERT INTO DEPARTMENT VALUES ('SALES',2,1235);
INSERT INTO DEPARTMENT VALUES ('ACCOUNTING',3,1236);
INSERT INTO DEPARTMENT VALUES ('MANAGEMENT',4,1237);

INSERT INTO CUSTOMER VALUES (4321, 'ANTREAS', 'ANTREOU','MALE','PAFOS','GEROSKIPOU','VIZANTIOU','3',2525,TO_DATE('04.04.1954','DD.MM.YYYY'));
INSERT INTO CUSTOMER VALUES (4322, 'MARIA', 'ANTREOU','FEMALE','PAFOS','GEROSKIPOU','VIZANTIOU','7',2525,TO_DATE('03.03.1953','DD.MM.YYYY'));
INSERT INTO CUSTOMER VALUES (4323, 'KOSTAS', 'ANTREOU','MALE','PAFOS','GEROSKIPOU','VIZANTIOU','3',2525,TO_DATE('04.04.1970','DD.MM.YYYY'));
INSERT INTO CUSTOMER VALUES (4324, 'ELENA', 'ANTREOU','MALE','PAFOS','GEROSKIPOU','VIZANTIOU','3',2252,TO_DATE('04.04.1985','DD.MM.YYYY'));
INSERT INTO CUSTOMER VALUES (4325, 'MARIOS', 'ANTREOU','MALE','PAFOS','GEROSKIPOU','VIZANTIOU','3',2525,TO_DATE('04.04.1987','DD.MM.YYYY'));

INSERT INTO CAR VALUES (1111111111, 'MAZDA', '3', 'BLUE', 'MANUAL', 'PETROL', 1600, TO_DATE('08.01.2013','DD.MM.YYYY'), 5000, 'CASH', 2, 4321);
INSERT INTO CAR VALUES (2222222222, 'TOYOTA', 'COROLLA','BLACK', 'MANUAL','PETROL',1600,TO_DATE('08.01.2013','DD.MM.YYYY'),7000,'FINANCE',2,4322);
INSERT INTO CAR VALUES (3333333333, 'TOYOTA', 'HILUX', 'GRAY', '4X4', 'DIESEL', 2500, TO_DATE('08.01.2013','DD.MM.YYYY'), 10000, 'CASH', 2 , 4325);
INSERT INTO CAR VALUES (4444444444, 'HONDA', 'CIVIC', 'BLACK', 'MANUAL', 'PETROL', 1300, TO_DATE('08.01.2013','DD.MM.YYYY'), 3000,'CASH', 2, 4323);
INSERT INTO CAR VALUES (5555555555, 'HONDA', 'INSIGHT', 'WHITE', 'AUTO', 'PETROL', 1500, TO_DATE('08.01.2013','DD.MM.YYYY'), 20000, 'FINANCE', 2, 4324);

INSERT INTO CAR_PARTS VALUES ('A20005B', 'RIGHT FRONT AXLE', 200);
INSERT INTO CAR_PARTS VALUES ('C15220C', 'AIR FILTER', 50);
INSERT INTO CAR_PARTS VALUES ('V99T', 'ENGINE GASKET', 80);
INSERT INTO CAR_PARTS VALUES ('A20004B', 'LEFT FRONT AXLE', 200);
INSERT INTO CAR_PARTS VALUES ('U9P002', 'INTAKE MANIFOLD', 300);

INSERT INTO USED_CAR VALUES (4444444444, 25000, TO_DATE('02.02.2000','DD.MM.YYYY'));
INSERT INTO USED_CAR VALUES (5555555555, 122000, TO_DATE('05.08.2003','DD.MM.YYYY'));

INSERT INTO SERVICE VALUES (TO_DATE('12.01.2013','DD.MM.YYYY'), TO_DATE('01.01.2013','DD.MM.YYYY'), 300, 0001, 4321, 'CASH', 1111111111);

INSERT INTO CUST_PHONUMBER VALUES (99123456, 4321, 'MOBILE');
INSERT INTO CUST_PHONUMBER VALUES (99849563, 4322, 'MOBILE');
INSERT INTO CUST_PHONUMBER VALUES (22568988, 4323, 'LANDLINE');
INSERT INTO CUST_PHONUMBER VALUES (25698974, 4324, 'LANDLINE');
INSERT INTO CUST_PHONUMBER VALUES (97584692, 4325, 'MOBILE');

INSERT INTO CAR_DEFECTS VALUES ('BROKEN SIDE MIRRORS|OIL LEAKS', 4444444444);
INSERT INTO CAR_DEFECTS VALUES ('RUSTY PAINT|WORN-OUT CLUTCH', 5555555555);

INSERT INTO EMPLOYEE_PHONUMBER VALUES (97989565, 1234);
INSERT INTO EMPLOYEE_PHONUMBER VALUES (97585858, 1235);
INSERT INTO EMPLOYEE_PHONUMBER VALUES (96525263, 1236);
INSERT INTO EMPLOYEE_PHONUMBER VALUES (97484852, 1237);

INSERT INTO SERVICE_DETAILS VALUES (0001, 'BROKEN FRONT RIGHT AXLE', 'REPLACED FRONT RIGHT AXLE');

INSERT INTO HAS VALUES ('A20005B', 1111111111);
INSERT INTO HAS VALUES ('A20004B', 1111111111);

INSERT INTO USES VALUES ('A20005B', 0001);
serge
  • 366
  • 1
  • 4
  • 22
  • "I get an error because the value of one's foreign key, has not been entered as a primary key of the other [...] How to bypass that problem?" - that's not really a good idea in a `relational database`. You _can_ turn off referential integrity - but then you have `orphan` data. – Simon Martin Jan 11 '13 at 21:08
  • 3
    You really need to order the insert statements correctly so that you insert the data into the foreign table first. – Vincent Ramdhanie Jan 11 '13 at 21:11
  • The problem is just that. Lets take Department and Employee. Each one has a foreign key referencing the other – serge Jan 11 '13 at 21:13
  • Or you can try disabling the constraints temporarily like [this](http://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle). – Vincent Ramdhanie Jan 11 '13 at 21:13
  • I just had an idea!! What if I insert the data BEFORE the "ALTER TABLE" block? Seems that this could do the trick I think. – serge Jan 11 '13 at 21:17
  • OK, i used the inserting before the table altering. That worked. Thank you for the responses. – serge Jan 11 '13 at 21:27
  • 3
    Another option is to declare constraints as "DEFERRED" and then they will be checked when you `commit`, not when you `insert`. –  Jan 11 '13 at 22:02
  • Check the order of the execution Insert according to your foreign key constrain – Yasa Oct 03 '13 at 06:03

0 Answers0