CREATE TABLE companies1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR( 255 ) NOT NULL UNIQUE,
    time_created TIMESTAMP
);

CREATE TABLE employees1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR( 255 ) NOT NULL,
    company_id INTEGER NOT NULL REFERENCES companies1( id ),
    UNIQUE( name, company_id )
);

-- -----------------------------------------

CREATE TABLE companies2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR( 255 ) NOT NULL UNIQUE,
    time_created TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE employees2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR( 255 ) NOT NULL CHECK( char_length( name ) > 7 ),
    company_id INTEGER NOT NULL REFERENCES companies2( id ) ON DELETE CASCADE,
    UNIQUE( name, company_id )
);

-- -----------------------------------------

CREATE TABLE authors1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR( 255 ) NOT NULL UNIQUE,
    post_count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE posts1 (
    id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES authors1( id ),
    text VARCHAR( 2048 ) NOT NULL
);

CREATE TABLE authors2 (
    id SERIAL PRIMARY KEY,
    name VARCHAR( 255 ) NOT NULL UNIQUE,
    post_count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE posts2 (
    id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES authors2( id ),
    text VARCHAR( 2048 ) NOT NULL
);

CREATE RULE update_post_count AS
ON DELETE TO posts2
DO ALSO (
    UPDATE
        authors2
    SET
        post_count = (
            SELECT COUNT(*)
            FROM posts2
            WHERE author_id = OLD.author_id
        ) - 1  -- subtract the post about to be deleted
    WHERE
        id = OLD.author_id
    ;
);