Programmer's Blog

Programmer's reference

Category Archives: SQL

[SQL] move column position

ALTERTABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;
ALTERTABLE Employees MODIFY COLUMN empName VARCHAR(50) FIRST;
Advertisements

[OracleDB] Drop and add constraints

To ensure integrity drop option is placed before add option

ALTER TABLE <TABLENAME> DROP CONSTRAINT pk_primkey;

ALTER TABLE <TABLENAME> ADD CONSTRAINT pk_primkey primary key (id);

[OracleDB] Check constraints of table

There are several types of constraints in oracle e.g. PK (primary key), FK (foreign key),  UK (unique keys), CK (check if values comply), etc. Once a constraint is registered in the database, they will be consolidated in a table called user_constraints.

The following SQL is to check the constraint by table_name or constraint_name

SQL> SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME ='<TABLENAME>';
SQL> SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME LIKE '%ID%';

The schema of the table user_constraints:
 Name                   Null?    Type
 --------------------- -------- ------------------------------------
 OWNER                  NOT NULL 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)

[OracleDB] Modify column

Different from normal sql, the modification of oracle DB is using the MODIFY syntax rather than the ALTER COLUMN

SQL> ALTER TABLE <TABLENAME> MODIFY(<COLUMNNAME> NULL);
SQL> ALTER TABLE <TABLENAME> MODIFY(<COLUMNNAME> NOT NULL);
SQL> ALTER TABLE <TABLENAME> MODIFY(<COLUMNNAME> VARCAHR2(200));