CREATE SCHEMA rset;

CREATE TABLE rset.d_rs_crop_type (
    rs_crop_type_id Integer NOT NULL,
    rs_crop_type_name VarChar (60) NOT NULL,
    CONSTRAINT rs_crop_type_id PRIMARY KEY (rs_crop_type_id)
);

CREATE TABLE rset.rs_crop (
    rs_crop_id Integer NOT NULL,
    rs_cover_1 VarChar (120),
    rs_cover_2 VarChar (120),
    rs_crop_name VarChar (120) NOT NULL,
    rs_crop_order Integer,
    rs_crop_type_id Integer,
    rs_crop_units VarChar (60),
    rs_lbs_per_unit Numeric,
    rs_crop_pct_dmat Numeric,
    rs_pct_nitrogen Numeric,
    rs_nitrogen_yield Numeric,
    rs_pct_phosphorus Numeric,
    rs_phosphorus_yield Numeric,
    rs_residue_score Numeric NOT NULL,
    CONSTRAINT rs_crop_id PRIMARY KEY (rs_crop_id),
    CONSTRAINT rs_crop_type_fk FOREIGN KEY (rs_crop_type_id) REFERENCES rset.d_rs_crop_type(rs_crop_type_id)
);

CREATE INDEX fki_rs_crop_type_id ON rset.rs_crop (rs_crop_type_id);

--ALTER TABLE ONLY rs_crop
--    ADD CONSTRAINT rs_crop_type_id FOREIGN KEY (rs_crop_type_id) REFERENCES d_rs_crop_type(rs_crop_type_id);


