Hi all, I'm new to Oracle, I'm trying to migrate from PostgreSQL to Oracle. I have this table in PostgreSQL that I would like to convert to Oracle. There are a couple of errors here:1) I can't create the CHECK constraints on date_start and date_end, because Oracle doesn't allow this syntax.
Is there an work around for this?2) I would like to have the last_modify column updated, every time someone update the row. In Pg I had the pgplsql update_last_modify() and the reg_on_update_set_last_modify trigger, but I can't transalte them to PL/PSQL.
3) I would like to create an index on rows having type column in 'A' or 'P' but 'C'.
Could you please give me some help? CREATE TABLE reg ( code VARCHAR2(10) PRIMARY KEY, name VARCHAR2(100), type CHAR CHECK (type IN ('A','C','P'), date_start DATE CHECK (date_start <= CURRENT_DATE), date_end DATE, bdn_id NUMBER, bdn_cod_reg VARCHAR2(10), last_modify TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT reg_date_end_check CHECK (date_end BETWEEN date_start AND CURRENT_DATE)
); CREATE FUNCTION update_last_modify() RETURNS trigger AS $$ BEGIN NEW.last_modify := CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER reg_on_update_set_last_modify BEFORE UPDATE ON reg FOR EACH ROW EXECUTE PROCEDURE update_last_modify();CREATE UNIQUE INDEX uniq_name on reg (name, type) where type = 'A' and typo ='P';