SQL - SET DEFAULT doesn't work when deleting a value

Question

I have 2 tables:

CREATE TABLE REP
(
      PRIMARY KEY REP_NUM CHAR(2) DEFAULT ('00'),
      LAST_NAME CHAR(15),
      FIRST_NAME CHAR(15),
      STREET CHAR(15),
      CITY CHAR(15),
      PROVINCE CHAR(3),
      ZIP CHAR(5),
      COMMISSION DECIMAL(7,2),
      RATE DECIMAL(3,2) 
);

And:

CREATE TABLE CUSTOMER
(
    CUSTOMER_NUM CHAR(3) PRIMARY KEY,
    FOREIGN KEY (REP_NUM) REFERENCES REP ON DELETE SET DEFAULT,
    CUSTOMER_NAME CHAR(35) NOT NULL,
    STREET CHAR(15),
    CITY CHAR(15),
    PROVINCE CHAR(3),
    ZIP CHAR(5),
    BALANCE DECIMAL(8,2),
    CREDIT_LIMIT DECIMAL(8,2),
    REP_NUM CHAR(2) 
);

As you can see, the REP_NUM column is the primary key in the REP table and a foreign key in the CUSTOMER table.

For example, if I enter new values into the REP table with the value of REP_NUM being 35, and also insert new values to the CUSTOMER table with REP_NUM being 35, and then I delete it from the REP table:

DELETE FROM REP WHERE REP_NUM = '35';

I want the REP_NUM value in the CUSTOMER table (foreign key) to now be 00.

However, when I run this code, it doesn't work. Instead of getting '00', when I delete '35', I just get a "blank value" in the REP_NUM attribute of the CUSTOMER table.

Any suggestions?

Thanks!

Answer 1

You have several issues: REP-table:

• a default for a primary key doesn't make sense, it will fail the 2nd time you use it

CUSTOMER-table

• You don't have a default on REP_NUM, your main problem in this topic
• Your foreign key references table REP, but on what column?

Both tables:

• A CHAR() field using the abbreviation "number" doesn't make sense. CHAR(2) is also very limited in the amount of options.

This one works:

CREATE TABLE REP
(
      REP_NUM CHAR(2) PRIMARY KEY -- No default, doesn't make sence, it will FAIL the 2nd time you use it.
    -- other columns
);

CREATE TABLE CUSTOMER
(
    CUSTOMER_NUM CHAR(3) PRIMARY KEY,
    FOREIGN KEY (REP_NUM) REFERENCES REP(REP_NUM) ON DELETE SET DEFAULT, -- mention the column as well!
    REP_NUM CHAR(2) DEFAULT '00'
    -- other columns
);

INSERT INTO rep(rep_num) VALUES('00');
INSERT INTO rep(rep_num) VALUES('35');

INSERT INTO customer(CUSTOMER_NUM, rep_num) VALUES('x', default); -- results in 'a'
INSERT INTO customer(CUSTOMER_NUM, rep_num) VALUES('y', '35');

DELETE FROM rep WHERE rep_num = '35';