I am prevented from inserting data into a column because of a foreign key violation. However, the foreign key is a valid reference

30 Views Asked by At

#Step 02

CREATE TABLE company_customer
(
    cust_ID INT,
    address_num VARCHAR (6),
    street VARCHAR(20),
    city VARCHAR(20),
    cust_contact_num VARCHAR(12) UNIQUE NOT NULL,
    cust_alt_contact VARCHAR(12),
    cust_email VARCHAR(20) UNIQUE NOT NULL,
    company_name VARCHAR(20) NOT NULL,
    company_reg_num VARCHAR(25) UNIQUE NOT NULL,
    CONSTRAINT ccus_pk PRIMARY KEY (cust_ID)
    
);

#Step 03

CREATE TABLE individual_customer
(
    cust_ID INT ,
    address_num VARCHAR (6),
    street VARCHAR(20),
    city VARCHAR(20),
    cust_contact_num VARCHAR(12) UNIQUE NOT NULL,
    cust_alt_contact VARCHAR(12),
    cust_email VARCHAR(20)UNIQUE NOT NULL,
    cust_nic VARCHAR(13)UNIQUE NOT NULL,
    c_fname VARCHAR(15)NOT NULL,
    c_lname VARCHAR(15)NOT NULL,
    CONSTRAINT icus_pk PRIMARY KEY (cust_ID)

);

#Step 04

CREATE TABLE project
(
    project_ID INTEGER NOT NULL,
    cust_ID INTEGER,
    site_ad_num VARCHAR (6)NOT NULL,
    site_street VARCHAR(20) NOT NULL,
    site_city VARCHAR(20) NOT NULL,
    project_type VARCHAR(15) NOT NULL,
    proj_start_date DATE NOT NULL,
    estimated_completion_date DATE NOT NULL,
    actual_completion_date DATE,
    current_progress VARCHAR(15)NOT NULL,
    specific_requirements VARCHAR (40),
    CONSTRAINT prog_ID_pk PRIMARY KEY (project_ID),
    CONSTRAINT proj_icusID_fk FOREIGN KEY (cust_ID) REFERENCES individual_customer(cust_ID),
    CONSTRAINT proj_ccusID_fk FOREIGN KEY (cust_ID) REFERENCES company_customer(cust_ID)

);

INSERT INTO project 

(project_ID,cust_ID,site_ad_num, site_street,site_city ,project_type, proj_start_date, estimated_completion_date,actual_completion_date, current_progress, specific_requirements)

VALUES 

(301, 100, '123', 'Beach Road', 'Galle', 'CONSTRUCTION', '2020-04-06', '2020-05-17', null, 'On hold', 'Need additional labour.'),

(302,700,'99/7','Dehiwala Road', 'Maharagama','CONSTRUCTION','2020-08-09','2021-10-11',null,'On hold', 'Payment issue.'),

(303, 305, '456', 'Hillside Avenue', 'Galle', 'RENOVATION', '2021-01-01', '2021-12-12', null, 'Ongoing', null),

(304,205, '67J', 'Temple Road', 'Kottawa', 'CONSTRUCTION', '2021-02-03','2021-08-10', NULL, 'Ongoing', null),

(305, 205, '789', 'Beachfront Road', 'Negombo', 'RENOVATION', '2022-01-02', '2023-09-10', NULL, 'Ongoing',null),

(306,505,'67A','Bangalawatta Road', 'Kandy','CONSTRUCTION','2022-01-23','2023-09-26', '2023-08-29','Complete', null),

(307, 500,'21A', 'Coastal Road', 'Batticaloa', 'RENOVATION', '2023-01-02', '2023-09-05', '2023-10-12', 'Complete', null),

(308,705, '123B', 'Lake View Street', 'Colombo', 'CONSTRUCTION', '2023-01-01', '2023-12-30','2023-12-15', 'Complete', null),

(309, 105, '987/23', 'Hilltop Drive', 'Kottawa','RENOVATION', '2023-09-02', '2024-12-12', NULL, 'Ongoing', null),

(310,400, '45', 'Dabahen Road','Kandy', 'CONSTRUCTION', '2024-01-01','2024-03-30','2024-03-29', 'Complete', null),

(311, 500, '345B', 'Hill Road', 'Colombo', 'RENOVATION', '2024-01-03', '2025-01-02', null, 'On hold', 'Awaiting governent approval.'),

(312,600,'24/56','Samagi Road', 'Homagama','CONSTRUCTION','2024-03-03','2025-01-20',null,'Ongoing',null),

(313,600,'123A','Sinha Road','Dehiwala','RENOVATION','2024-01-30','2025-05-09',null, 'Ongoing',null),

(314,500,'34C','Barnse Road', 'Colombo','RENOVATION','2024-01-01','2024-03-20','2024-03-19', 'Complete',null),

(315,205,'209','Kingsley Road','Colombo','RENOVATION','2024-01-20','2024-04-01','2024-04-02','Complete',null);

if I disable the foriegn key check it runs,I want it to be run without unticking the box

0

There are 0 best solutions below