TABLE CREATION AND MODIFICATION
1. CREATE A TABLE WITH A PRIMARY KEY
CREATE TABLE CUSTOMERS (
CUSTOMER_ID NUMBER PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(100)
);
2. CREATE A TABLE WITH A COMPOSITE PRIMARY KEY
CREATE TABLE ORDER_ITEMS (
ORDER_ID NUMBER,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
PRIMARY KEY (ORDER_ID, PRODUCT_ID)
);
3. ADD A PRIMARY KEY TO AN EXISTING TABLE
ALTER TABLE ORDERS
ADD CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID);
4. DROP A PRIMARY KEY CONSTRAINT
ALTER TABLE ORDERS
DROP CONSTRAINT PK_ORDER_ID;
5. RENAME A PRIMARY KEY CONSTRAINT
ALTER TABLE ORDERS
RENAME CONSTRAINT PK_ORDER_ID TO PK_ORDERS_ID;
6. MODIFY A TABLE TO ADD A PRIMARY KEY WITH A SPECIFIC NAME
ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID);
7. CREATE A TABLE WITH A PRIMARY KEY AND DEFAULT VALUE
CREATE TABLE PRODUCTS (
PRODUCT_ID NUMBER PRIMARY KEY,
PRODUCT_NAME VARCHAR2(100),
CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP
);
QUERYING PRIMARY KEYS
8. LIST ALL PRIMARY KEY CONSTRAINTS IN THE USERS SCHEMA
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
9. GET COLUMNS OF A SPECIFIC PRIMARY KEY
SELECT COLS.COLUMN_NAME
FROM USER_CONS_COLUMNS COLS
JOIN USER_CONSTRAINTS CONS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
WHERE CONS.CONSTRAINT_TYPE = 'P'
AND CONS.TABLE_NAME = 'EMPLOYEES';
10. QUERY ALL PRIMARY KEYS IN THE DATABASE
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
11. CHECK IF A COLUMN IS PART OF A PRIMARY KEY
SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE COLUMN_NAME = 'EMPLOYEE_ID'
AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
);
12. GET ALL TABLES WITH THEIR PRIMARY KEYS
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
13. SHOW ALL CONSTRAINTS FOR A TABLE
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'ORDERS';
14. QUERY PRIMARY KEY CONSTRAINTS WITH COLUMN NAMES
SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
FROM USER_CONSTRAINTS A
JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P';
15. LIST ALL PRIMARY KEYS WITH OWNER INFORMATION
SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
MANAGING PRIMARY KEYS
16. ENABLE A PRIMARY KEY CONSTRAINT
ALTER TABLE EMPLOYEES
ENABLE CONSTRAINT PK_EMPLOYEE_ID;
17. DISABLE A PRIMARY KEY CONSTRAINT
ALTER TABLE EMPLOYEES
DISABLE CONSTRAINT PK_EMPLOYEE_ID;
18. VALIDATE A PRIMARY KEY CONSTRAINT
ALTER TABLE EMPLOYEES
ENABLE VALIDATE CONSTRAINT PK_EMPLOYEE_ID;
19. DROP PRIMARY KEY AND RECREATE IT
ALTER TABLE EMPLOYEES
DROP CONSTRAINT PK_EMPLOYEE_ID;
ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (EMPLOYEE_ID);
20. USE `DBMS_METADATA` TO GET DDL OF A PRIMARY KEY
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', 'PK_EMPLOYEE_ID')
FROM DUAL;
21. CREATE A PRIMARY KEY WITH A FOREIGN KEY REFERENCE
CREATE TABLE PROJECTS (
PROJECT_ID NUMBER PRIMARY KEY,
MANAGER_ID NUMBER,
FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
);
22. FIND ALL CONSTRAINTS RELATED TO A TABLE
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'ORDERS';
23. CHECK FOR ORPHAN RECORDS WHEN A PRIMARY KEY IS DROPPED
SELECT *
FROM ORDER_ITEMS OI
WHERE OI.ORDER_ID NOT IN (SELECT O.ORDER_ID FROM ORDERS O);
ADVANCED QUERIES
24. CREATE A PRIMARY KEY WITH A CHECK CONSTRAINT
CREATE TABLE SHIPMENTS (
SHIPMENT_ID NUMBER PRIMARY KEY,
SHIPMENT_DATE DATE CHECK (SHIPMENT_DATE IS NOT NULL)
);
25. CREATE A TABLE WITH MULTIPLE CONSTRAINTS
CREATE TABLE SUPPLIERS (
SUPPLIER_ID NUMBER PRIMARY KEY,
SUPPLIER_NAME VARCHAR2(100) UNIQUE,
CITY VARCHAR2(50)
);
26. USE A SEQUENCE TO GENERATE PRIMARY KEY VALUES
CREATE SEQUENCE SUPPLIER_ID_SEQ
START WITH 1
INCREMENT BY 1;
CREATE TABLE SUPPLIERS (
SUPPLIER_ID NUMBER PRIMARY KEY,
SUPPLIER_NAME VARCHAR2(100),
CITY VARCHAR2(50)
);
INSERT INTO SUPPLIERS (SUPPLIER_ID, SUPPLIER_NAME, CITY)
VALUES (SUPPLIER_ID_SEQ.NEXTVAL, 'SUPPLIER A', 'CITY A');
27. CHECK FOR PRIMARY KEY VIOLATIONS
SELECT EMPLOYEE_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY EMPLOYEE_ID
HAVING COUNT(*) > 1;
28. GET THE DDL FOR ALL TABLES WITH PRIMARY KEYS
SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME)
FROM USER_TABLES
WHERE TABLE_NAME IN (
SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
);
29. CREATE A PRIMARY KEY WITH AUTO INCREMENT (USING A TRIGGER)
CREATE TABLE AUTO_INCR_TABLE (
ID NUMBER,
NAME VARCHAR2(100),
PRIMARY KEY (ID)
);
CREATE OR REPLACE TRIGGER AUTO_INCR_TRIGGER
BEFORE INSERT ON AUTO_INCR_TABLE
FOR EACH ROW
BEGIN
SELECT AUTO_INCR_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
30. CREATE A UNIQUE INDEX ON PRIMARY KEY
CREATE UNIQUE INDEX IDX_UNIQUE_EMP_ID ON EMPLOYEES(EMPLOYEE_ID);
MAINTENANCE AND ADMINISTRATION
31. EXPORT DDL OF ALL PRIMARY KEYS IN THE SCHEMA
SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME)
FROM USER_TABLES
WHERE TABLE_NAME IN (
SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
);
32. CREATE A PRIMARY KEY AND HANDLE DUPLICATES
ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (EMPLOYEE_ID)
USING INDEX (
CREATE UNIQUE INDEX EMP_ID_IDX ON EMPLOYEES(EMPLOYEE_ID)
WHERE EMPLOYEE_ID IS NOT NULL
);
33. CHECK PRIMARY KEY INTEGRITY ACROSS TABLES
SELECT *
FROM EMPLOYEES E
WHERE NOT EXISTS (
SELECT 1 FROM ORDERS O WHERE O.EMPLOYEE_ID = E.EMPLOYEE_ID
);
34. CREATE A VIEW TO SHOW PRIMARY KEY COLUMNS
CREATE VIEW PK_COLUMNS AS
SELECT A.TABLE_NAME, B.COLUMN_NAME
FROM USER_CONSTRAINTS A
JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P';
DYNAMIC SQL AND PROGRAMMING
35. DYNAMIC SQL TO DROP AND CREATE A PRIMARY KEY
DECLARE
V_SQL VARCHAR2(1000);
BEGIN
V_SQL := 'ALTER TABLE EMPLOYEES DROP CONSTRAINT PK_EMPLOYEE_ID';
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (EMPLOYEE_ID)';
EXECUTE IMMEDIATE V_SQL;
END;
36. CREATE A PROCEDURE TO ADD PRIMARY KEY
CREATE OR REPLACE PROCEDURE ADD_PRIMARY_KEY(P_TABLE_NAME IN VARCHAR2, P_KEY_NAME IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' ADD CONSTRAINT ' || P_KEY_NAME || ' PRIMARY KEY (ID)';
END;
37. FUNCTION TO CHECK IF PRIMARY KEY EXISTS
CREATE OR REPLACE FUNCTION PK_EXISTS(P_TABLE_NAME IN VARCHAR2, P_CONSTRAINT_NAME IN VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN (SELECT COUNT(*)
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = P_TABLE_NAME
AND CONSTRAINT_NAME = P_CONSTRAINT_NAME
AND CONSTRAINT_TYPE = 'P');
END;
ADDITIONAL OPERATIONS
38. LIST COLUMNS THAT ARE NOT PART OF ANY PRIMARY KEY
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
AND COLUMN_NAME NOT IN
(
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
)
);
39. DROP A PRIMARY KEY AND ALL RELATED CONSTRAINTS
ALTER TABLE ORDERS
DROP CONSTRAINT PK_ORDER_ID CASCADE;
40. ADD A COMMENT TO A PRIMARY KEY
COMMENT ON CONSTRAINT PK_EMPLOYEE_ID IS 'PRIMARY KEY FOR EMPLOYEE TABLE';
41. COUNT DISTINCT VALUES IN PRIMARY KEY COLUMN
SELECT COUNT(DISTINCT EMPLOYEE_ID) AS DISTINCT_EMPLOYEE_COUNT
FROM EMPLOYEES;
42. QUERY FOR PRIMARY KEY LENGTH
SELECT A.TABLE_NAME, B.COLUMN_NAME, B.DATA_LENGTH
FROM USER_CONSTRAINTS A
JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P';
43. CREATE A VIEW SHOWING TABLES WITH PRIMARY KEYS
CREATE VIEW TABLES_WITH_PRIMARY_KEYS AS
SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
44. SHOW PRIMARY KEY VIOLATION ERRORS
SELECT *
FROM USER_ERRORS
WHERE TYPE = 'CONSTRAINT'
AND NAME = 'PK_EMPLOYEE_ID';
BEST PRACTICES AND OPTIMIZATION
45. OPTIMIZE PRIMARY KEY INDEX
ALTER INDEX PK_EMPLOYEE_ID_IDX REBUILD;
46. ANALYZE TABLES TO UPDATE STATISTICS ON PRIMARY KEYS
ANALYZE TABLE EMPLOYEES COMPUTE STATISTICS;
47. CHECK FOR DUPLICATE ENTRIES BEFORE ADDING A PRIMARY KEY
SELECT EMPLOYEE_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY EMPLOYEE_ID
HAVING COUNT(*) > 1;
48. USE A CTE TO FIND NON-UNIQUE PRIMARY KEY CANDIDATES
WITH DUPLICATES AS (
SELECT EMPLOYEE_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY EMPLOYEE_ID
HAVING COUNT(*) > 1
)
SELECT *
FROM DUPLICATES;
49. LIST PRIMARY KEY CONSTRAINTS BY CREATION DATE
SELECT CONSTRAINT_NAME, TABLE_NAME, CREATED
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
ORDER BY CREATED;
50. GENERATE DDL FOR ALL PRIMARY KEYS
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME)
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
FEEL FREE TO ASK IF YOU NEED CLARIFICATIONS ON ANY SPECIFIC QUERIES OR TOPICS!
No comments:
Post a Comment