I'm in doubt, when I create a CONSTRAINT UNIQUE , does it evaluate all the columns together?
For example, I want a country not to have names or acronyms , in which case should I create CONSTRAINT different?
Because I've done only one, it lets me insert countries since name and acronym are not the same, however, I want to block even if only one of these columns is the same.
I did so:
CREATE TABLE PAIS
(
IdPais INTEGER NOT NULL,
NomePais VARCHAR(60) NOT NULL,
SiglaPais VARCHAR(3) NOT NULL,
StatusPais CHAR(1) NOT NULL,
CONSTRAINT PAIS PRIMARY KEY (IdPais),
CONSTRAINT PAIS_UNIQUE UNIQUE (NomePais, SiglaPais)
);
/
CREATE SEQUENCE SEQ_ID_PAIS
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
NOCACHE
CYCLE;
/
CREATE OR REPLACE TRIGGER TRG_ID_PAIS BEFORE INSERT ON PAIS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.IDPAIS IS NULL THEN SELECT SEQ_ID_PAIS.NEXTVAL INTO :NEW.IDPAIS FROM DUAL; END IF; END COLUMN_SEQUENCES; END;
/