drop table suministrar;
drop table Pieza;
drop table Proveedor;

CREATE TABLE Pieza      (
  clvPieza  NUMBER(9) CONSTRAINT PK_Pieza      PRIMARY KEY,
  nombPieza CHAR(32)  CONSTRAINT NN_nombPieza  NOT NULL
                      CONSTRAINT UP_nombPieza  CHECK(nombPieza = UPPER(nombPieza)),
  color	CHAR(32)      CONSTRAINT UP_color      CHECK(color = UPPER(color)));

CREATE TABLE Proveedor  (
  clvProv   NUMBER(9) CONSTRAINT PK_Proveedor  PRIMARY KEY,
  nombProv  CHAR(32)  CONSTRAINT NN_nombProv   NOT NULL
                      CONSTRAINT UN_nombProv   UNIQUE
                      CONSTRAINT UP_nombProv   CHECK(nombProv = UPPER(nombProv)));

CREATE TABLE suministrar (
  clvProv   NUMBER(9),
  clvPieza  NUMBER(9),
  CONSTRAINT PK_Suministrar PRIMARY KEY (clvProv, clvPieza),
  CONSTRAINT FK_SuminProv   FOREIGN KEY (clvProv) REFERENCES Proveedor(clvProv) ON DELETE CASCADE,
  CONSTRAINT FK_SuminPieza  FOREIGN KEY (clvPieza)  REFERENCES Pieza(clvPieza)  ON DELETE CASCADE);
  
ALTER TABLE Pieza MODIFY nombPieza CONSTRAINT NN_nombPieza NOT NULL;
ALTER TABLE Pieza DROP CONSTRAINT NN_nombPieza;
ALTER TABLE Suministrar ADD (cantidad NUMBER(4) CONSTRAINT NN_cantidad NOT NULL);
ALTER TABLE Suministrar DISABLE CONSTRAINT FK_SuminProv;
ALTER TABLE Suministrar ENABLE  CONSTRAINT FK_SuminProv;

CREATE TABLE PiezasRojas      (refPieza, tipoPieza)   AS
   SELECT clvPieza, nombPieza FROM Pieza  WHERE color = 'ROJO';
   
CREATE SYNONYM misPiezas FOR Santiago.Pieza;


SELECT TABLE_NAME FROM USER_TABLES;
SELECT * FROM CAT;

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS
  FROM USER_CONSTRAINTS;

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS
  FROM USER_CONSTRAINTS
  WHERE TABLE_NAME = 'Pieza';

SELECT b.OWNER, a.CONSTRAINT_NAME, a.TABLE_NAME, b.COLUMN_NAME, b.POSITION
  FROM USER_CONSTRAINTS A, ALL_CONS_COLUMNS B
  WHERE (b.CONSTRAINT_NAME = a.CONSTRAINT_NAME) AND (a.CONSTRAINT_TYPE = 'P');