Oracle tables

gistfile1.sql

-- Create the base tables
CREATE TABLE products(
    product_id NUMBER GENERATED ALWAYS AS IDENTITY CONSTRAINT products_product_id_pk PRIMARY KEY,
    name VARCHAR2(50),
    description CLOB,
    price NUMBER(9, 2),
    location MDSYS.SDO_GEOMETRY
)

CREATE TABLE images(
    image_id NUMBER GENERATED ALWAYS AS IDENTITY CONSTRAINT images_image_id_pk PRIMARY KEY,
    product_id NUMBER CONSTRAINT images_product_id_fk REFERENCES products(product_id) ON DELETE SET NULL,
    filename VARCHAR2(100),
    image ORDSYS.ORDImage,
    thumbnail BLOB
)

-- Add a context index on products to use full text search
CREATE INDEX products_name_ctx
ON products(name)
INDEXTYPE IS ctxsys.context
PARAMETERS ('SYNC(ON COMMIT)');

-- Added a postcode field after some development
ALTER TABLE products
ADD (
    postcode VARCHAR2(8) NOT NULL
);

-- Added an archived field to products
ALTER TABLE products
ADD (
    archived CHAR DEFAULT 'N' CHECK(archived IN ('N','Y'))
);

-- Forgot to add not null contraints, woops
ALTER TABLE products MODIFY (name NOT NULL);
ALTER TABLE products MODIFY (description NOT NULL);
ALTER TABLE products MODIFY (price NOT NULL);
ALTER TABLE products MODIFY (location NOT NULL);

ALTER TABLE images MODIFY (filename NOT NULL);