Constraints

 Creating Tables with Constraints

===========================================

-- 1. Create table with PRIMARY KEY

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    emp_name VARCHAR2(50)

);

 

-- 2. Create table with NOT NULL constraint

CREATE TABLE departments (

    dept_id NUMBER,

    dept_name VARCHAR2(50) NOT NULL

);

 

-- 3. Create table with UNIQUE constraint

CREATE TABLE projects (

    proj_id NUMBER,

    proj_name VARCHAR2(100) UNIQUE

);

 

-- 4. Create table with CHECK constraint

CREATE TABLE products (

    product_id NUMBER,

    price NUMBER CHECK (price > 0)

);

 

-- 5. Create table with FOREIGN KEY constraint

CREATE TABLE orders (

    order_id NUMBER PRIMARY KEY,

    cust_id NUMBER,

    CONSTRAINT fk_customer FOREIGN KEY (cust_id) REFERENCES customers(customer_id)

);

 

-- 6. Create table with composite PRIMARY KEY

CREATE TABLE employee_projects (

    emp_id NUMBER,

    proj_id NUMBER,

    PRIMARY KEY (emp_id, proj_id)

);

 

-- 7. Create table with multiple constraints inline

CREATE TABLE vendors (

    vendor_id NUMBER CONSTRAINT pk_vendor PRIMARY KEY,

    vendor_name VARCHAR2(100) CONSTRAINT unq_vendor_name UNIQUE NOT NULL,

    rating NUMBER CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5)

);

 

-- 8. Create table with named NOT NULL constraint

CREATE TABLE locations (

    loc_id NUMBER CONSTRAINT nn_loc_id NOT NULL,

    loc_name VARCHAR2(50)

);

 

-- 9. Create table with foreign key referencing composite key

CREATE TABLE employee_tasks (

    emp_id NUMBER,

    task_id NUMBER,

    CONSTRAINT fk_emp_proj FOREIGN KEY (emp_id, task_id) REFERENCES employee_projects(emp_id, proj_id)

);

 

-- 10. Create table with default value and check constraint

CREATE TABLE accounts (

    acc_id NUMBER PRIMARY KEY,

    balance NUMBER DEFAULT 0 CHECK (balance >= 0)

);

 

Adding Constraints to Existing Tables

===========================================

 

-- 11. Add PRIMARY KEY constraint

ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);

 

-- 12. Add UNIQUE constraint

ALTER TABLE employees ADD CONSTRAINT unq_emp_name UNIQUE (emp_name);

 

-- 13. Add NOT NULL constraint

ALTER TABLE departments MODIFY dept_name NOT NULL;

 

-- 14. Add CHECK constraint

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);

 

-- 15. Add FOREIGN KEY constraint

ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (cust_id) REFERENCES customers(customer_id);

 

-- 16. Add composite UNIQUE constraint

ALTER TABLE employee_projects ADD CONSTRAINT unq_emp_proj UNIQUE (emp_id, proj_id);

 

-- 17. Add CHECK constraint with complex condition

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary BETWEEN 1000 AND 10000);

 

-- 18. Add NOT NULL to a column (using MODIFY)

ALTER TABLE vendors MODIFY vendor_name NOT NULL;

 

-- 19. Add FOREIGN KEY with ON DELETE CASCADE

ALTER TABLE orders ADD CONSTRAINT fk_cust_del FOREIGN KEY (cust_id) REFERENCES customers(customer_id) ON DELETE CASCADE;

 

-- 20. Add CHECK constraint for email format

ALTER TABLE employees ADD CONSTRAINT chk_email CHECK (email LIKE '%_@__%.__%');

 

Dropping Constraints

===========================================

 

-- 21. Drop PRIMARY KEY

ALTER TABLE employees DROP CONSTRAINT pk_emp;

 

-- 22. Drop UNIQUE constraint

ALTER TABLE employees DROP CONSTRAINT unq_emp_name;

 

-- 23. Drop CHECK constraint

ALTER TABLE products DROP CONSTRAINT chk_price;

 

-- 24. Drop FOREIGN KEY constraint

ALTER TABLE orders DROP CONSTRAINT fk_cust;

 

-- 25. Drop NOT NULL constraint (by modifying column)

ALTER TABLE departments MODIFY dept_name NULL;

 

-- 26. Drop composite UNIQUE constraint

ALTER TABLE employee_projects DROP CONSTRAINT unq_emp_proj;

 

-- 27. Drop CHECK constraint with complex condition

ALTER TABLE employees DROP CONSTRAINT chk_salary;

 

-- 28. Drop FOREIGN KEY with ON DELETE CASCADE

ALTER TABLE orders DROP CONSTRAINT fk_cust_del;

 

-- 29. Drop NOT NULL constraint on vendor_name

ALTER TABLE vendors MODIFY vendor_name NULL;

 

-- 30. Drop CHECK constraint for email format

ALTER TABLE employees DROP CONSTRAINT chk_email;

 

Constraint Queries and Information

===========================================

 

-- 31. List all constraints on a table

SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'EMPLOYEES';

 

-- 32. Find columns involved in a constraint

SELECT constraint_name, column_name FROM user_cons_columns WHERE constraint_name = 'PK_EMP';

 

-- 33. Find all tables with primary keys

SELECT table_name FROM user_constraints WHERE constraint_type = 'P';

 

-- 34. Find foreign keys referencing a table

SELECT constraint_name, table_name FROM user_constraints WHERE r_constraint_name = 'PK_CUSTOMERS';

 

-- 35. Check if a column is NOT NULL

SELECT column_name, nullable FROM user_tab_columns WHERE table_name = 'DEPARTMENTS' AND column_name = 'DEPT_NAME';

 

-- 36. Check all CHECK constraints

SELECT constraint_name, search_condition FROM user_constraints WHERE constraint_type = 'C';

 

-- 37. Check all UNIQUE constraints

SELECT constraint_name, table_name FROM user_constraints WHERE constraint_type = 'U';

 

-- 38. Find all foreign keys for a table

SELECT constraint_name, r_constraint_name FROM user_constraints WHERE table_name = 'ORDERS' AND constraint_type = 'R';

 

-- 39. Get all constraints created by user

SELECT constraint_name, constraint_type, table_name FROM user_constraints;

 

-- 40. Show constraint status (enabled/disabled)

SELECT constraint_name, status FROM user_constraints WHERE table_name = 'EMPLOYEES';

 

Creating Constraints with Specific Options

===========================================

 

-- 41. Add foreign key with ON DELETE SET NULL

ALTER TABLE orders ADD CONSTRAINT fk_cust_setnull FOREIGN KEY (cust_id) REFERENCES customers(customer_id) ON DELETE SET NULL;

 

-- 42. Create deferrable constraint

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) DEFERRABLE INITIALLY DEFERRED;

 

-- 43. Disable a constraint

ALTER TABLE employees DISABLE CONSTRAINT fk_dept;

 

-- 44. Enable a constraint

ALTER TABLE employees ENABLE CONSTRAINT fk_dept;

 

-- 45. Create unique constraint using index

CREATE UNIQUE INDEX unq_idx_emp_name ON employees(emp_name);

ALTER TABLE employees ADD CONSTRAINT unq_emp_name UNIQUE USING INDEX unq_idx_emp_name;

 

-- 46. Create check constraint for allowed statuses

ALTER TABLE orders ADD CONSTRAINT chk_status CHECK (status IN ('OPEN', 'CLOSED', 'PENDING'));

 

-- 47. Add primary key using constraint name

ALTER TABLE products ADD CONSTRAINT pk_product PRIMARY KEY (product_id);

 

-- 48. Create NOT NULL constraint with name on existing column

ALTER TABLE departments MODIFY dept_name CONSTRAINT nn_dept_name NOT NULL;

 

-- 49. Create foreign key referencing self (hierarchy)

ALTER TABLE employees ADD CONSTRAINT fk_mgr FOREIGN KEY (manager_id) REFERENCES employees(emp_id);

 

-- 50. Create check constraint with multiple columns

ALTER TABLE employee_projects ADD CONSTRAINT chk_proj_date CHECK (start_date < end_date);

 

 Advanced Constraint Examples

===========================================

-- 51. Add unique constraint with function-based index

CREATE UNIQUE INDEX unq_upper_emp_name ON employees(UPPER(emp_name));

ALTER TABLE employees ADD CONSTRAINT unq_emp_name_unique UNIQUE USING INDEX unq_upper_emp_name;

 

-- 52. Create check constraint for positive integer IDs

ALTER TABLE products ADD CONSTRAINT chk_prod_id CHECK (product_id > 0);

 

-- 53. Create NOT NULL constraint on multiple columns

ALTER TABLE employees MODIFY (emp_name NOT NULL, email NOT NULL);

 

-- 54. Add foreign key with DEFERRABLE INITIALLY IMMEDIATE

ALTER TABLE orders ADD CONSTRAINT fk_cust DEFERRABLE INITIALLY IMMEDIATE FOREIGN KEY (cust_id) REFERENCES customers(customer_id);

 

-- 55. Add check constraint to ensure email contains '@'

ALTER TABLE employees ADD CONSTRAINT chk_email_valid CHECK (INSTR(email, '@') > 1);

 

-- 56. Disable all constraints on a table

ALTER TABLE employees DISABLE CONSTRAINT ALL;

 

-- 57. Enable all constraints on a table

ALTER TABLE employees ENABLE CONSTRAINT ALL;

 

-- 58. Drop primary key and create new one on another column

ALTER TABLE employees DROP PRIMARY KEY;

ALTER TABLE employees ADD CONSTRAINT pk_new_emp PRIMARY KEY (email);

 

-- 59. Add foreign key with ON DELETE NO ACTION (default)

ALTER TABLE orders ADD CONSTRAINT fk_cust_noaction FOREIGN KEY (cust_id) REFERENCES customers(customer_id) ON DELETE NO ACTION;

 

-- 60. Add check constraint using PL/SQL function (not recommended)

ALTER TABLE employees ADD CONSTRAINT chk_salary_func CHECK (salary_check(salary) = 1);

 

Checking Constraint Violations & Testing

===========================================

 

-- 61. Insert row violating PRIMARY KEY (error expected)

INSERT INTO employees(emp_id, emp_name) VALUES (1, 'John Doe');

INSERT INTO employees(emp_id, emp_name) VALUES (1, 'Jane Smith');

 

-- 62. Insert row violating UNIQUE constraint

INSERT INTO projects(proj_id, proj_name) VALUES (1, 'Project A');

INSERT INTO projects(proj_id, proj_name) VALUES (2, 'Project A');

 

-- 63. Insert row violating CHECK constraint

INSERT INTO products(product_id, price) VALUES (1, -10);

 

-- 64. Insert row violating FOREIGN KEY constraint

INSERT INTO orders(order_id, cust_id) VALUES (1, 9999);

 

-- 65. Insert row violating NOT NULL constraint

INSERT INTO departments(dept_id, dept_name) VALUES (1, NULL);

 

-- 66. Insert row passing all constraints

INSERT INTO employees(emp_id, emp_name) VALUES (2, 'Alice');

 

-- 67. Attempt to disable a PRIMARY KEY constraint (not allowed)

ALTER TABLE employees DISABLE CONSTRAINT pk_emp;

 

-- 68. Validate all constraints on a table

ALTER TABLE employees VALIDATE CONSTRAINT ALL;

 

-- 69. Validate a specific constraint

ALTER TABLE products VALIDATE CONSTRAINT chk_price;

 

-- 70. Enable NOVALIDATE constraint (accepts existing violations)

ALTER TABLE orders ENABLE NOVALIDATE CONSTRAINT fk_cust;

 

 Managing Constraints with Indexes

===========================================

-- 71. Create unique index for unique constraint

CREATE UNIQUE INDEX idx_emp_name ON employees(emp_name);

 

-- 72. Drop unique index (without dropping constraint)

DROP INDEX idx_emp_name;

 

-- 73. Add unique constraint using existing index

ALTER TABLE employees ADD CONSTRAINT unq_emp_name UNIQUE USING INDEX idx_emp_name;

 

-- 74. Rebuild index associated with constraint

ALTER INDEX idx_emp_name REBUILD;

 

-- 75. Drop constraint with CASCADE (drops dependent constraints)

ALTER TABLE employees DROP CONSTRAINT fk_mgr CASCADE;

 

-- 76. Disable unique constraint without dropping index

ALTER TABLE employees DISABLE CONSTRAINT unq_emp_name;

 

-- 77. Enable unique constraint using index

ALTER TABLE employees ENABLE CONSTRAINT unq_emp_name;

 

-- 78. Create primary key using index

CREATE UNIQUE INDEX idx_pk_emp ON employees(emp_id);

ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY USING INDEX idx_pk_emp;

 

-- 79. Create function-based index for unique constraint

CREATE UNIQUE INDEX idx_emp_email_upper ON employees(UPPER(email));

ALTER TABLE employees ADD CONSTRAINT unq_emp_email UNIQUE USING INDEX idx_emp_email_upper;

 

-- 80. Create unique constraint with inline syntax (table creation)

CREATE TABLE customers (

    customer_id NUMBER PRIMARY KEY,

    email VARCHAR2(100) UNIQUE

);

 

 Constraints and Data Integrity Utilities

===========================================

-- 81. Enable constraint NOVALIDATE (constraint active, but existing data not checked)

ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT chk_salary;

 

-- 82. Disable constraint NOVALIDATE

ALTER TABLE employees DISABLE NOVALIDATE CONSTRAINT chk_salary;

 

-- 83. Check if constraint is deferrable

SELECT constraint_name, deferrable FROM user_constraints WHERE table_name = 'EMPLOYEES';

 

-- 84. Check if constraint is validated

SELECT constraint_name, status FROM user_constraints WHERE table_name = 'PRODUCTS';

 

-- 85. Disable all foreign keys temporarily for data load

BEGIN

  FOR r IN (SELECT constraint_name FROM user_constraints WHERE constraint_type='R' AND table_name='EMPLOYEES') LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE employees DISABLE CONSTRAINT ' || r.constraint_name;

  END LOOP;

END;

/

 

-- 86. Enable all foreign keys after data load

BEGIN

  FOR r IN (SELECT constraint_name FROM user_constraints WHERE constraint_type='R' AND table_name='EMPLOYEES') LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE employees ENABLE CONSTRAINT ' || r.constraint_name;

  END LOOP;

END;

/

 

-- 87. Rename a constraint

ALTER TABLE employees RENAME CONSTRAINT old_constraint_name TO new_constraint_name;

 

-- 88. Add NOT NULL constraint without a name

ALTER TABLE employees MODIFY email NOT NULL;

 

-- 89. Disable NOT NULL constraint (by making column nullable)

ALTER TABLE employees MODIFY email NULL;

 

-- 90. Get all NOT NULL constraints by querying columns

SELECT table_name, column_name FROM user_tab_columns WHERE nullable = 'N';

 

Miscellaneous Constraint Queries & Examples

===========================================

-- 91. Create unique constraint on multiple columns

ALTER TABLE employee_projects ADD CONSTRAINT unq_emp_proj UNIQUE (emp_id, proj_id);

 

-- 92. Create check constraint for date range

ALTER TABLE projects ADD CONSTRAINT chk_proj_dates CHECK (start_date <= end_date);

 

-- 93. Add primary key on VARCHAR2 column

CREATE TABLE countries (

    country_code VARCHAR2(2) PRIMARY KEY,

    country_name VARCHAR2(100)

);

 

-- 94. Create foreign key referencing multiple columns

ALTER TABLE order_details ADD CONSTRAINT fk_order_prod FOREIGN KEY (order_id, product_id) REFERENCES orders(order_id, product_id);

 

-- 95. Drop all constraints for a table (script)

BEGIN

  FOR r IN (SELECT constraint_name FROM user_constraints WHERE table_name = 'EMPLOYEES') LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE employees DROP CONSTRAINT ' || r.constraint_name;

  END LOOP;

END;

/

 

-- 96. Create a deferred primary key constraint

ALTER TABLE employees ADD CONSTRAINT pk_emp_def PRIMARY KEY (emp_id) DEFERRABLE INITIALLY DEFERRED;

 

-- 97. Insert a row violating a deferrable constraint but commit succeeds when constraint is deferred

SET CONSTRAINTS ALL DEFERRED;

INSERT INTO employees(emp_id, emp_name) VALUES (null, 'Test');

COMMIT;

 

-- 98. Create check constraint with REGEXP_LIKE (Oracle 11g+)

ALTER TABLE employees ADD CONSTRAINT chk_phone CHECK (REGEXP_LIKE(phone, '^\d{3}-\d{3}-\d{4}$'));

 

-- 99. Disable and enable all constraints on a schema

BEGIN

  FOR r IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type IN ('P','U','R','C')) LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE ' || r.table_name || ' DISABLE CONSTRAINT ' || r.constraint_name;

  END LOOP;

END;

/

 

-- 100. Enable all constraints on a schema

BEGIN

  FOR r IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type IN ('P','U','R','C')) LOOP

    EXECUTE IMMEDIATE 'ALTER TABLE ' || r.table_name || ' ENABLE CONSTRAINT ' || r.constraint_name;

  END LOOP;

END;

/