Adding a column with a default value set to the number of occurence in another table thats already f-CodePudding

Let's say i have these two tables which are already filled, and i would like to add a column nb_departments to the table Locations, which is initialized with the amount of departments in the location.

CREATE TABLE departments
(
    department_id   NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id      NUMBER(6),
    location_id     NUMBER(4)    NOT NULL
);

CREATE TABLE locations
(
    location_id    NUMBER(4) PRIMARY KEY,
    street_address VARCHAR2(40),
    postal_code    VARCHAR2(12),
    city           VARCHAR2(30),
    state_province VARCHAR2(25),
    country_id     CHAR(2) NOT NULL
);

Could it be done using a trigger, or using alter table add column default value?

CodePudding user response：

You could create a view:

CREATE VIEW vwlocations 
(
    location_id,
    street_address,
    postal_code,
    city ,
    state_province,
    country_id,
    nb_departments
) AS
SELECT  l.location_id,
        l.street_address,
        l.postal_code,
        l.city ,
        l.state_province,
        l.country_id,
        COUNT(d.location_id) AS nb_departments
FROM    locations l
        JOIN departments d
            ON l.location_id = d.location_id
GROUP BY l.location_id,
        l.street_address,
        l.postal_code,
        l.city ,
        l.state_province,
        l.country_id

If you did want to add a column to the existing table, you can initialize your column values this way and add a trigger to manage updates / inserts / deletes thereafter:

UPDATE locations SET locations.nb_departments = (SELECT COUNT(*) FROM departments WHERE locations.location_id = departments.location_id)