/* @BD_Piezas_Pruebas.sql */

SET ECHO ON
SET LINESIZE 132
SET PAGESIZE 80
SET WRAP OFF

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;

column OWNER           format   a10
column CONSTRAINT_NAME format   a20
column TABLE_NAME      format   a16
column DEFERRABLE      format   a14
column DEFERRED        format   a10
column VALIDATED       format   a10

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS, DEFERRABLE, DEFERRED, VALIDATED
  FROM USER_CONSTRAINTS
  ORDER BY OWNER, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME;

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');
