SQL | Comprobación de restricciones existentes en una tabla mediante diccionarios de datos

Requisito previo : Restricciones de SQL

En SQL Server, el diccionario de datos es un conjunto de tablas de bases de datos que se utilizan para almacenar información sobre la definición de una base de datos. Uno puede usar estos diccionarios de datos para verificar las restricciones en una tabla ya existente y cambiarlas (si es posible).

• Diccionario de datos USER_CONSTRAINTS: este diccionario de datos contiene información sobre cada restricción utilizada en una base de datos junto con información específica de la restricción.

  DESC USER_CONSTRAINTS;
  
  Name              Null     Type         
  ----------------- -------- ------------ 
  OWNER                      VARCHAR2(30) 
  CONSTRAINT_NAME   NOT NULL VARCHAR2(30) 
  CONSTRAINT_TYPE            VARCHAR2(1)  
  TABLE_NAME        NOT NULL VARCHAR2(30) 
  SEARCH_CONDITION           LONG         
  R_OWNER                    VARCHAR2(30) 
  R_CONSTRAINT_NAME          VARCHAR2(30) 
  DELETE_RULE                VARCHAR2(9)  
  STATUS                     VARCHAR2(8)  
  DEFERRABLE                 VARCHAR2(14) 
  DEFERRED                   VARCHAR2(9)  
  VALIDATED                  VARCHAR2(13) 
  GENERATED                  VARCHAR2(14) 
  BAD                        VARCHAR2(3)  
  RELY                       VARCHAR2(4)  
  LAST_CHANGE                DATE         
  INDEX_OWNER                VARCHAR2(30) 
  INDEX_NAME                 VARCHAR2(30) 
  INVALID                    VARCHAR2(7)  
  VIEW_RELATED               VARCHAR2(14) 
  

  Los tipos de restricciones son:

  C - Check constraint on a table  
  P - Primary key  
  U - Unique key  
  R - Referential integrity  
  V - With check option, on a view  
  O - With read only, on a view  
  H - Hash expression  
  F - Constraint that involves a REF column  
  S - Supplemental logging
  

  Ahora considere la siguiente tabla fuente «SDF»:

  SUPPNO  SNAME   STATUS  CITY
  21    JONYY    25    NY
  22    MIKKY    11    LA
  23    JIM    29    LV
  24    BNFERYY    47    HW
  25    TIM    41    HS
  

  Consulta para verificar las restricciones en esta tabla:

  SELECT CONSTRAINT_NAME, SEARCH_CONDITION AS CONSTRAINT_TYPE 
  FROM USER_CONSTRAINTS 
  WHERE TABLE_NAME='SDF';
  

  Salida :

  CONSTRAINT_NAME	CONSTRAINT_TYPE
  XYZ	ESTADO<50
  A B C	(NULO)

• Diccionario de datos USER_CONS_COLUMNS: podemos usar este diccionario de datos para encontrar las columnas a las que se ha aplicado la restricción.

  DESC USER_CONS_COLUMNS;
  
  Name            Null     Type           
  --------------- -------- -------------- 
  OWNER           NOT NULL VARCHAR2(30)   
  CONSTRAINT_NAME NOT NULL VARCHAR2(30)   
  TABLE_NAME      NOT NULL VARCHAR2(30)   
  COLUMN_NAME              VARCHAR2(4000) 
  POSITION                 NUMBER     
  

  Consulta para verificar las columnas de la tabla SDF con restricciones:

  SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='SDF';
  

  Salida :

  DUEÑO	CONSTRAINT_NAME	NOMBRE DE LA TABLA	COLUMN_NAME	POSICIÓN
  SISTEMA	XYZ	SDF	ESTADO	(nulo)
  SISTEMA	A B C	SDF	ESTADO	1

• Uso del producto cartesiano para obtener información completa sobre las restricciones:

  SELECT A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, B.COLUMN_NAME, B.TABLE_NAME
  FROM 
  USER_CONSTRAINTS A,
  USER_CONS_COLUMNS B
  WHERE A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
  AND A.TABLE_NAME='SDF';
  

  Salida :

  CONSTRAINT_NAME	CONSTRAINT_TYPE	COLUMN_NAME	NOMBRE DE LA TABLA
  XYZ	C	ESTADO	SDF
  A B C	PAGS	SUPPNO	SDF