1-20: Creating Tables with Constraints
-- 1. Create table with PRIMARY KEY
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100)
);
-- 2. Create table with UNIQUE constraint
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100) UNIQUE
);
-- 3. Create table with CHECK constraint
CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
price NUMBER CHECK (price > 0)
);
-- 4. Create table with NOT NULL constraint
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL
);
-- 5. Create table with FOREIGN KEY constraint
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- 6. Create table with multiple constraints
CREATE TABLE inventory (
item_id NUMBER PRIMARY KEY,
item_name VARCHAR2(100) UNIQUE NOT NULL,
quantity NUMBER CHECK (quantity >= 0)
);
-- 7. Create table with composite PRIMARY KEY
CREATE TABLE enrollment (
student_id NUMBER,
course_id NUMBER,
CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);
-- 8. Create table with composite UNIQUE constraint
CREATE TABLE user_roles (
user_id NUMBER,
role_id NUMBER,
CONSTRAINT uq_user_role UNIQUE (user_id, role_id)
);
-- 9. Create table with named CHECK constraint
CREATE TABLE accounts (
account_id NUMBER PRIMARY KEY,
balance NUMBER,
CONSTRAINT chk_balance CHECK (balance >= 0)
);
-- 10. Create table with NOT NULL on multiple columns
CREATE TABLE employees_info (
emp_id NUMBER PRIMARY KEY,
email VARCHAR2(100) NOT NULL,
phone VARCHAR2(15) NOT NULL
);
-- 11. Create table with FOREIGN KEY and ON DELETE CASCADE
CREATE TABLE order_items (
order_item_id NUMBER PRIMARY KEY,
order_id NUMBER,
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);
-- 12. Create table with FOREIGN KEY and ON DELETE SET NULL
CREATE TABLE project_assignments (
assignment_id NUMBER PRIMARY KEY,
project_id NUMBER,
employee_id NUMBER,
CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees(emp_id) ON DELETE SET NULL
);
-- 13. Create table with CHECK constraint on multiple columns
CREATE TABLE salary (
emp_id NUMBER PRIMARY KEY,
base_salary NUMBER,
bonus NUMBER,
CONSTRAINT chk_salary CHECK (base_salary >= 0 AND bonus >= 0)
);
-- 14. Create table with NOT NULL and CHECK constraints combined
CREATE TABLE products_details (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
stock NUMBER CHECK (stock >= 0) NOT NULL
);
-- 15. Create table with UNIQUE and NOT NULL constraints
CREATE TABLE users (
user_id NUMBER PRIMARY KEY,
username VARCHAR2(50) UNIQUE NOT NULL,
password VARCHAR2(50) NOT NULL
);
-- 16. Create table with FOREIGN KEY referencing composite PRIMARY KEY
CREATE TABLE student_courses (
student_id NUMBER,
course_id NUMBER,
grade VARCHAR2(2),
CONSTRAINT fk_student_course FOREIGN KEY (student_id, course_id) REFERENCES enrollment(student_id, course_id)
);
-- 17. Create table with PRIMARY KEY and foreign key constraints
CREATE TABLE payment (
payment_id NUMBER PRIMARY KEY,
order_id NUMBER,
CONSTRAINT fk_order_payment FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
-- 18. Create table with CHECK using function
CREATE TABLE employees_salary (
emp_id NUMBER PRIMARY KEY,
salary NUMBER CHECK (salary > 0 AND salary < 1000000)
);
-- 19. Create table with unique constraint on multiple columns
CREATE TABLE employee_projects (
emp_id NUMBER,
project_id NUMBER,
CONSTRAINT uq_emp_proj UNIQUE (emp_id, project_id)
);
-- 20. Create table with NOT NULL, UNIQUE, and CHECK constraints
CREATE TABLE vendor (
vendor_id NUMBER PRIMARY KEY,
vendor_name VARCHAR2(100) UNIQUE NOT NULL,
rating NUMBER CHECK (rating BETWEEN 1 AND 5)
);
________________________________________
21-40: Adding Constraints to Existing Tables
-- 21. Add PRIMARY KEY constraint
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);
-- 22. Add UNIQUE constraint
ALTER TABLE departments ADD CONSTRAINT uq_dept_name UNIQUE (dept_name);
-- 23. Add CHECK constraint
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);
-- 24. Add NOT NULL constraint (by modifying column)
ALTER TABLE customers MODIFY (customer_name NOT NULL);
-- 25. Add FOREIGN KEY constraint
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
-- 26. Add composite PRIMARY KEY
ALTER TABLE enrollment ADD CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id);
-- 27. Add composite UNIQUE constraint
ALTER TABLE user_roles ADD CONSTRAINT uq_user_role UNIQUE (user_id, role_id);
-- 28. Add CHECK constraint on multiple columns
ALTER TABLE salary ADD CONSTRAINT chk_salary CHECK (base_salary >= 0 AND bonus >= 0);
-- 29. Add FOREIGN KEY with ON DELETE CASCADE
ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE;
-- 30. Add FOREIGN KEY with ON DELETE SET NULL
ALTER TABLE project_assignments ADD CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees(emp_id) ON DELETE SET NULL;
-- 31. Add NOT NULL constraint to multiple columns
ALTER TABLE employees_info MODIFY (email NOT NULL);
ALTER TABLE employees_info MODIFY (phone NOT NULL);
-- 32. Add UNIQUE constraint to multiple columns
ALTER TABLE employee_projects ADD CONSTRAINT uq_emp_proj UNIQUE (emp_id, project_id);
-- 33. Add CHECK constraint using function
ALTER TABLE employees_salary ADD CONSTRAINT chk_salary CHECK (salary > 0 AND salary < 1000000);
-- 34. Add CHECK constraint on rating
ALTER TABLE vendor ADD CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5);
-- 35. Add FOREIGN KEY referencing composite PK
ALTER TABLE student_courses ADD CONSTRAINT fk_student_course FOREIGN KEY (student_id, course_id) REFERENCES enrollment(student_id, course_id);
-- 36. Add FOREIGN KEY with CASCADE DELETE and UPDATE
ALTER TABLE order_items ADD CONSTRAINT fk_order CASCADE DELETE;
-- 37. Add PRIMARY KEY on single column
ALTER TABLE payment ADD CONSTRAINT pk_payment PRIMARY KEY (payment_id);
-- 38. Add UNIQUE constraint on single column
ALTER TABLE users ADD CONSTRAINT uq_username UNIQUE (username);
-- 39. Add NOT NULL constraint on email
ALTER TABLE employees_info MODIFY (email NOT NULL);
-- 40. Add CHECK constraint on stock
ALTER TABLE products_details ADD CONSTRAINT chk_stock CHECK (stock >= 0);
________________________________________
41-60: Disabling, Enabling, and Dropping Constraints
-- 41. Disable a constraint
ALTER TABLE employees DISABLE CONSTRAINT pk_emp;
-- 42. Enable a constraint
ALTER TABLE employees ENABLE CONSTRAINT pk_emp;
-- 43. Drop a constraint
ALTER TABLE employees DROP CONSTRAINT pk_emp;
-- 44. Disable all constraints on table
ALTER TABLE orders DISABLE CONSTRAINT ALL;
-- 45. Enable all constraints on table
ALTER TABLE orders ENABLE CONSTRAINT ALL;
-- 46. Drop all constraints on a table (use with caution)
ALTER TABLE orders DROP CONSTRAINT fk_customer;
-- 47. Disable FOREIGN KEY constraint
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
-- 48. Enable FOREIGN KEY constraint
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;
-- 49. Drop UNIQUE constraint
ALTER TABLE departments DROP CONSTRAINT uq_dept_name;
-- 50. Drop CHECK constraint
ALTER TABLE products DROP CONSTRAINT chk_price;
-- 51. Disable CHECK constraint
ALTER TABLE products DISABLE CONSTRAINT chk_price;
-- 52. Enable CHECK constraint
ALTER TABLE products ENABLE CONSTRAINT chk_price;
-- 53. Disable NOT NULL constraint (requires column modification)
ALTER TABLE customers MODIFY (customer_name NULL);
-- 54. Enable NOT NULL constraint
ALTER TABLE customers MODIFY (customer_name NOT NULL);
-- 55. Disable composite PRIMARY KEY
ALTER TABLE enrollment DISABLE CONSTRAINT pk_enrollment;
-- 56. Enable composite PRIMARY KEY
ALTER TABLE enrollment ENABLE CONSTRAINT pk_enrollment;
-- 57. Drop composite UNIQUE constraint
ALTER TABLE user_roles DROP CONSTRAINT uq_user_role;
-- 58. Drop FOREIGN KEY with CASCADE DELETE
ALTER TABLE order_items DROP CONSTRAINT fk_order;
-- 59. Drop FOREIGN KEY referencing composite PK
ALTER TABLE student_courses DROP CONSTRAINT fk_student_course;
-- 60. Drop CHECK constraint with function
ALTER TABLE employees_salary DROP CONSTRAINT chk_salary;
________________________________________
61-80: Querying Constraint Metadata from Data Dictionary Views
-- 61. List all constraints in the database (for user)
SELECT constraint_name, constraint_type, table_name
FROM user_constraints;
-- 62. List all constraints on a table
SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
-- 63. List all PRIMARY KEY constraints
SELECT constraint_name, table_name
FROM user_constraints
WHERE constraint_type = 'P';
-- 64. List all FOREIGN KEY constraints
SELECT constraint_name, table_name, r_constraint_name
FROM user_constraints
WHERE constraint_type = 'R';
-- 65. List all UNIQUE constraints
SELECT constraint_name, table_name
FROM user_constraints
WHERE constraint_type = 'U';
-- 66. List all CHECK constraints
SELECT constraint_name, table_name, search_condition
FROM user_constraints
WHERE constraint_type = 'C';
-- 67. List columns involved in constraints
SELECT constraint_name, column_name
FROM user_cons_columns;
-- 68. List columns for a particular constraint
SELECT column_name
FROM user_cons_columns
WHERE constraint_name = 'PK_EMP';
-- 69. List foreign key relationships (child and parent tables)
SELECT a.table_name child_table,
a.constraint_name child_constraint,
c.table_name parent_table,
c.constraint_name parent_constraint
FROM user_constraints a
JOIN user_constraints c ON a.r_constraint_name = c.constraint_name
WHERE a.constraint_type = 'R';
-- 70. Check status of constraints on a table
SELECT constraint_name, status
FROM user_constraints
WHERE table_name = 'ORDERS';
-- 71. List all disabled constraints
SELECT constraint_name, table_name
FROM user_constraints
WHERE status = 'DISABLED';
-- 72. List all constraints with search condition
SELECT constraint_name, search_condition
FROM user_constraints
WHERE search_condition IS NOT NULL;
-- 73. List constraints with DEFERRABLE status
SELECT constraint_name, deferrable, deferred
FROM user_constraints;
-- 74. List NOT NULL constraints (found in USER_TAB_COLUMNS)
SELECT column_name, table_name
FROM user_tab_columns
WHERE nullable = 'N';
-- 75. List all foreign keys with referenced table and column
SELECT a.constraint_name,
a.table_name,
b.column_name,
c_pk.table_name ref_table,
d.column_name ref_column
FROM user_constraints a
JOIN user_cons_columns b ON a.constraint_name = b.constraint_name
JOIN user_constraints c_pk ON a.r_constraint_name = c_pk.constraint_name
JOIN user_cons_columns d ON c_pk.constraint_name = d.constraint_name
WHERE a.constraint_type = 'R';
-- 76. List all constraints created by a specific user
SELECT owner, constraint_name, constraint_type, table_name
FROM all_constraints
WHERE owner = USER;
-- 77. List constraints with their creation date (if audited)
SELECT constraint_name, table_name, status
FROM user_constraints
ORDER BY constraint_name;
-- 78. Find the number of constraints per table
SELECT table_name, COUNT(*) as num_constraints
FROM user_constraints
GROUP BY table_name;
-- 79. List constraints and their types with comments (if any)
SELECT c.constraint_name, c.constraint_type, t.comments
FROM user_constraints c
LEFT JOIN user_tab_comments t ON c.table_name = t.table_name;
-- 80. Find constraints with dependent indexes
SELECT c.constraint_name, i.index_name
FROM user_constraints c
JOIN user_indexes i ON c.index_name = i.index_name
WHERE c.constraint_type IN ('P', 'U');
________________________________________
81-100: Miscellaneous Useful Constraint Queries and Examples
-- 81. Create a deferred constraint (deferred checking)
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id) DEFERRABLE INITIALLY DEFERRED;
-- 82. Set constraint to immediate
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;
-- 83. Set constraint to deferred in a transaction
SET CONSTRAINTS ALL DEFERRED;
-- 84. Drop NOT NULL constraint by modifying column to nullable
ALTER TABLE employees MODIFY (emp_name NULL);
-- 85. Check if a column has a NOT NULL constraint
SELECT nullable FROM user_tab_columns WHERE table_name = 'EMPLOYEES' AND column_name = 'EMP_NAME';
-- 86. Rename a constraint (Oracle does not support directly; workaround is to drop and re-create)
-- 87. Temporarily disable a foreign key to perform data load
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
-- 88. Re-enable a disabled foreign key after data load
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;
-- 89. Validate a constraint after enabling
ALTER TABLE orders ENABLE VALIDATE CONSTRAINT fk_customer;
-- 90. Enable constraint without validation (not recommended)
ALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT fk_customer;
-- 91. Drop a primary key constraint along with the index
ALTER TABLE employees DROP PRIMARY KEY CASCADE;
-- 92. Query constraints with indexes on them
SELECT constraint_name, index_name
FROM user_constraints
WHERE index_name IS NOT NULL;
-- 93. Disable triggers along with constraints (some constraints use triggers internally)
ALTER TABLE orders DISABLE ALL TRIGGERS;
-- 94. Enable triggers
ALTER TABLE orders ENABLE ALL TRIGGERS;
-- 95. Identify constraints that are deferrable
SELECT constraint_name, deferrable, deferred FROM user_constraints WHERE deferrable = 'DEFERRABLE';
-- 96. Find the default values on columns (not constraints but useful)
SELECT column_name, data_default FROM user_tab_columns WHERE table_name = 'EMPLOYEES';
-- 97. Query for foreign keys that reference a given primary key table
SELECT constraint_name, table_name
FROM user_constraints
WHERE r_constraint_name = (SELECT constraint_name FROM user_constraints WHERE table_name = 'CUSTOMERS' AND constraint_type = 'P');
-- 98. Enable all constraints in a schema
BEGIN
FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE status = 'DISABLED') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || c.table_name || ' ENABLE CONSTRAINT ' || c.constraint_name;
END LOOP;
END;
/
-- 99. Check if a constraint exists before adding it (PL/SQL example)
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM user_constraints WHERE constraint_name = 'PK_EMP';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id)';
END IF;
END;
/
-- 100. Create a check constraint with a subquery (use function or materialized view)
-- Note: Oracle does not allow subquery in CHECK constraints directly, so use a function.
CREATE OR REPLACE FUNCTION check_salary(p_salary NUMBER) RETURN NUMBER IS
BEGIN
IF p_salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive');
END IF;
RETURN p_salary;
END;
/
CREATE TABLE employee_salary (
emp_id NUMBER PRIMARY KEY,
salary NUMBER CONSTRAINT chk_salary CHECK (check_salary(salary) = salary)
);
No comments:
Post a Comment