next question.
I have a product table with a 'category" column that I want to
maintain in a separate table.
CREATE TABLE products (
product_id INTEGER DEFAULT
nextval('product_id_seq'::regclass) NOT NULL,
name VARCHAR(60) NOT NULL,
category SMALLINT NOT NULL,
CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE TABLE products (
category_id INTEGER DEFAULT
nextval('category_id_seq'::regclass) NOT NULL,
name VARCHAR(20) NOT NULL,
CONSTRAINT category_id PRIMARY KEY (category_id)
);
Every product must have a category,
Since many (but not all) products have the same category I only want 1
table with unique categories.
To do the insert into the products table I need to retrieve or insert
the category_id in categories first.
Which means more code on my client app (if ($cat_id =
get_cat_id($cat)) }else { $cat_id = insert_cat($cat)})
Can I write a BEFORE ROW trigger for the products table to runs on
INSERT or UPDATE to
1. insert a new category & return the new category_id OR
2. return the existing category_id for the (to be inserted row)
Alan
I donproducts.category to be a foreign key that points to the uniqie
category_id id in the want to keep I need to do get the cate
