Should my table A have a Foreign Key from table B Primary Key and via verse?-CodePudding

I would like to know my below code, will it be able to work as my PRIMARY KEY for Airplane is Serial_No and FOREIGN KEY is taken from my Scheduled table, Scheduled_No. Via verse PRIMARY KEY for Scheduled will be Scheduled_No and FOREIGN KEY is taken from my Airplane table, Serial_No.

I try to insert value for my Scheduled table, but it gives an error that, is unable to take data from parent key which is my Serial_No.

Should I remove Serial_No as my FOREIGN KEY in the Scheduled table or is there any other way to do it?

/* CREATE TABLE FOR AIRPLANE */

CREATE TABLE AIRPLANE (
    Serial_No Varchar2(10) NOT NULL,
    Scheduled_No Varchar2(10) NOT NULL,
    Flight_No Varchar2(6) NOT NULL,
    Model_No Number(3) NOT NULL,
    Capacity Number(3) NOT NULL,
    Maintenance_Date DATE NULL,
    PRIMARY KEY (Serial_No)
    );



 /* CREATE TABLE FOR SCHEDULED */
 
 CREATE TABLE SCHEDULED (
    Scheduled_No Varchar2(10) NOT NULL,
    Serial_No Varchar2(10) NOT NULL,
    Route_No Varchar2(10) NOT NULL,
    Job_No Varchar2(10) NOT NULL,
    Flight_Fly_On DATE NOT NULL,
    PRIMARY KEY (Scheduled_No),
    FOREIGN KEY (Serial_No) REFERENCES AIRPLANE (Serial_No)
    );

/* ADDING FK FOR Scheduled_No FROM SCHEDULED */

ALTER TABLE AIRPLANE 
ADD FOREIGN KEY (Scheduled_No) REFERENCES SCHEDULED (Scheduled_No);

CodePudding user response：

You can do it; modify foreign key constraint (on airplane) so that it is deferrable (i.e. check whether everything is OK at time of commit, not insert).

Something like this:

SQL> CREATE TABLE AIRPLANE
  2  (
  3     Serial_No          VARCHAR2 (10) NOT NULL,
  4     Scheduled_No       VARCHAR2 (10) NOT NULL,
  5     Flight_No          VARCHAR2 (6) NOT NULL,
  6     Model_No           NUMBER (3) NOT NULL,
  7     Capacity           NUMBER (3) NOT NULL,
  8     Maintenance_Date   DATE NULL,
  9     PRIMARY KEY (Serial_No)
 10  );

Table created.

SQL> CREATE TABLE SCHEDULED
  2  (
  3     Scheduled_No    VARCHAR2 (10) NOT NULL,
  4     Serial_No       VARCHAR2 (10) NOT NULL,
  5     Route_No        VARCHAR2 (10) NOT NULL,
  6     Job_No          VARCHAR2 (10) NOT NULL,
  7     Flight_Fly_On   DATE NOT NULL,
  8     PRIMARY KEY (Scheduled_No),
  9     FOREIGN KEY (Serial_No) REFERENCES AIRPLANE (Serial_No)
 10  );

Table created.

Pay attention to this command:

SQL> ALTER TABLE AIRPLANE
  2     ADD CONSTRAINT fk_air_sch FOREIGN KEY (Scheduled_No)
  3            REFERENCES SCHEDULED (Scheduled_No)
  4            INITIALLY DEFERRED DEFERRABLE;

Table altered.

Testing: insert into airplane now works, although its parent scheduled_no row / value doesn't exist yet:

SQL> INSERT INTO airplane (serial_no,
  2                        scheduled_no,
  3                        flight_no,
  4                        model_no,
  5                        capacity,
  6                        maintenance_date)
  7       VALUES ('1',
  8               '100',
  9               'abc',
 10               5,
 11               100,
 12               SYSDATE);

1 row created.

Insert a row into the scheduled table. Foreign keys must match.

SQL> INSERT INTO scheduled (scheduled_no,
  2                         serial_no,
  3                         route_no,
  4                         job_no,
  5                         flight_fly_on)
  6       VALUES ('100',
  7               '1',
  8               'xyz',
  9               'job',
 10               SYSDATE);

1 row created.

Succeeded as well; now, commit:

SQL> COMMIT;

Commit complete.

SQL>

CodePudding user response：

You have circular references.

You should work out what your tables refer to:

An AIRPLANE represents the physical object which has a capacity, a unique identifier (i.e. the number on the tail) and is a particular make and model.
A SCHEDULE represents what the physical object does at a point in time. I.e. on a particular date it will be flying a particular route.

In this case, it does not make sense for an AIRPLANE to have a single Scheduled_No as there can be many points in time and the airplane can have different schedules at different times so you want there to be a one-to-many relationship between each airplane and its schedules.

There should not be a Scheduled_No column in the AIRPLANE table and, since there shouldn't be a column, there should not be the foreign key constraint. You just want the constraint on Serial_No going back in the other direction. Removing that column and the corresponding constraint will solve your issue of circular referential constraints.

/* CREATE TABLE FOR AIRPLANE */
CREATE TABLE AIRPLANE (
    Serial_No        Varchar2(10) NOT NULL,
    Flight_No        Varchar2(6) NOT NULL,
    Model_No         Number(3) NOT NULL,
    Capacity         Number(3) NOT NULL,
    Maintenance_Date DATE NULL,
    PRIMARY KEY (Serial_No)
);

/* CREATE TABLE FOR SCHEDULED */
 
CREATE TABLE SCHEDULED (
    Scheduled_No  Varchar2(10) NOT NULL,
    Serial_No     Varchar2(10) NOT NULL,
    Route_No      Varchar2(10) NOT NULL,
    Job_No        Varchar2(10) NOT NULL,
    Flight_Fly_On DATE NOT NULL,
    PRIMARY KEY (Scheduled_No),
    FOREIGN KEY (Serial_No) REFERENCES AIRPLANE (Serial_No)
);