problems to create multiple tables in Oracle

Question

problems to create multiple tables in Oracle

Navigation

#1 by (1 votes)

1

I'm new to using Oracle , I used firebird before. I would like to know, why I can not create multiple tables at once in the sql master of SQLdeveloper . It creates only the first table (Country).

I try to execute the following statements, to create all the tables at once. (in this case I'm only using 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;

sql oracle

asked by anonymous 18.05.2017 / 17:42

1 answer

base_url of Codeigniter 3 changing css call link and js Team table with 8 variables

score 1 · Accepted Answer

I do not remember if Oracle Database allows more than one object to have the same name. In the script you posted, the primary key for each table is the same as the 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 you do not need to name the constraint, 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 
);
/