problems to create multiple tables in Oracle

Question

I am beginner in the use of Oracle, wore the firebird before. I’d like to know, why I can’t create multiple tables at once in the SQLdeveloper. It creates only the first table (Country).

I try to run the following instructions, to create all tables at once. (in this case I’m using only 3 tables to not get too long).

CREATE TABLE PAIS
(
  IdPais INTEGER NOT NULL,
  NomePais VARCHAR(60) NOT NULL,
  SiglaPais VARCHAR(3) NOT NULL,
  StatusPais CHAR(1) NOT NULL 
);

ALTER TABLE PAIS ADD
(
  CONSTRAINT PAIS PRIMARY KEY (IdPais)
);

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;


CREATE TABLE ESTADO
(
  IdEstado INTEGER NOT NULL,
  IdPais INTEGER NOT NULL,
  NomeEstado VARCHAR(60)  NOT NULL,
  SiglaEstado VARCHAR(2)  NOT NULL,
  StatusEstado CHAR(1) NOT NULL 
);

ALTER TABLE ESTADO ADD
(
  CONSTRAINT ESTADO PRIMARY KEY (IdEstado)
);

CREATE SEQUENCE SEQ_ID_ESTADO
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
NOCACHE
CYCLE;

CREATE OR REPLACE TRIGGER TRG_ID_ESTADO BEFORE INSERT ON ESTADO FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.IDESTADO IS NULL THEN SELECT SEQ_ID_ESTADO.NEXTVAL INTO :NEW.IDESTADO FROM DUAL; END IF; END COLUMN_SEQUENCES; END;


CREATE TABLE CIDADE
(
  IdCidade INTEGER NOT NULL,
  IdEstado INTEGER NOT NULL,
  NomeCidade VARCHAR(60)  NOT NULL,  
  StatusCidade CHAR(1) NOT NULL 
);

ALTER TABLE CIDADE ADD
(
  CONSTRAINT CIDADE PRIMARY KEY (IdCidade)
);

CREATE SEQUENCE SEQ_ID_CIDADE
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
NOCACHE
CYCLE;

CREATE OR REPLACE TRIGGER TRG_ID_CIDADE BEFORE INSERT ON CIDADE FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.IDCIDADE IS NULL THEN SELECT SEQ_ID_CIDADE.NEXTVAL INTO :NEW.IDCIDADE FROM DUAL; END IF; END COLUMN_SEQUENCES; END;

Answer 1

I can’t remember if Oracle Database allows more than one object to have the same name. In the script you posted, the primary key of each table is with the same table name.

You can set the primary key in several ways:

--código #2
CREATE TABLE PAIS
(
  IdPais INTEGER NOT NULL,
  NomePais VARCHAR2(60) NOT NULL,
  SiglaPais VARCHAR2(3) NOT NULL,
  StatusPais CHAR(1) NOT NULL,
  constraint pkPAIS primary key (IdPais)
);
/

or

-- código #3
CREATE TABLE PAIS
(
  IdPais INTEGER NOT NULL,
  NomePais VARCHAR2(60) NOT NULL,
  SiglaPais VARCHAR2(3) NOT NULL,
  StatusPais CHAR(1) NOT NULL 
);
/
ALTER TABLE PAIS
  ADD pkPAIS primary key (IdPais);
/

If there is no need to name the restriction, you can also use this form:

-- código #1
CREATE TABLE PAIS
(
  IdPais INTEGER NOT NULL primary key,
  NomePais VARCHAR2(60) NOT NULL,
  SiglaPais VARCHAR2(3) NOT NULL,
  StatusPais CHAR(1) NOT NULL 
);
/