Advanced SQL Table Management and Integrity Constraints

Rapid Table Creation via Subqueries

  • SQL facilitates the creation of new tables based on existing ones through the use of subqueries (nested or inner queries).
  • The inner query is processed first, providing its output as input for the outer CREATE TABLE query.
  • Attributes, data characteristics, and existing rows are copied, but entity integrity (primary keys) and referential integrity (foreign keys) are not automatically transferred.
Syntax Variations by RDBMS
  • MySQL: Uses the AS keyword.   CREATE TABLE [IF NOT EXISTS] tablename AS SELECT ...
  • MS Access / SQL Server: Uses the INTO clause.   SELECT column_list INTO new_tablename FROM source_tablename;
Handling Existing Tables
  • In MySQL, using the optional IF NOT EXISTS clause prevents error messages if the table already exists, though it will not overwrite the table. Without this clause, an error is generated.
  • In MS Access, a prompt typically asks the user if they wish to delete and replace the existing table. SQL Server does not provide this automated deletion prompt.

SQL Indexes and Search Optimization

  • Indexes are database structures used to enhance search efficiency and maintain data integrity by preventing duplicate values across columns.
  • The DBMS automatically generates a unique index when a primary key is declared.
Creating Indexes
  • Basic syntax: CREATE [UNIQUE] INDEX indexname ON tablename(column1 [, column2]);
  • Unique Indexes: Used specifically for candidate keys to ensure no value is repeated.
    • Example: CREATE UNIQUE INDEX P_CODEX ON PRODUCT(P_CODE);
    • Attempting to insert a duplicate value results in a "duplicate value in index" error.
  • Composite Indexes: These involve multiple attributes and are often used to prevent duplication where a single attribute might repeat but a combination must remain unique.
    • Example: Prevent duplicate test dates for an employee using CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE, TEST_DATE);
Index Ordering and Deletion
  • Order: While the default result set for an index is ascending, descending order can be specified.
    • Example: CREATE INDEX PROD_PRICEX ON PRODUCT(P_PRICE DESC);
  • Deletion: The command used is DROP INDEX indexname.
    • Note: ANSI SQL requires only the index name, but some systems like MySQL require the table reference: DROP INDEX indexname ON tablename;

Modifying Table Structures with ALTER TABLE

  • The ALTER TABLE command and its keywords (ADD, MODIFY, DROP) are used to adjust table schemas after they have been created.
Column Management
  • Adding Columns: ALTER TABLE tablename ADD (columnname datatype);
    • Note: When adding a column to a table that already contains data, do not use the NOT NULL clause, as existing rows will default to null and trigger an error. The NOT NULL constraint can be added later once data is populated.
  • Modifying Columns: Used to change data types or widths.
    • Example (increasing width): ALTER TABLE PRODUCT MODIFY P_PRICE DECIMAL(9,2);
    • Example (changing data type): ALTER TABLE PRODUCT MODIFY V_CODE CHAR(5);
  • Deleting Columns: ALTER TABLE tablename DROP COLUMN columnname;
    • Restrictions: Most RDBMSs prevent dropping the last remaining column in a table or attributes involved in foreign key relationships.
RDBMS Specific Limitations
  • Oracle: Typically allows increasing a column's size but may restrict decreasing it to protect data integrity. Many data type changes are only permitted if the column contains no data (is empty).
  • Referential Integrity: Changing a data type on a column that is part of a foreign key relationship will often trigger errors if the change creates a mismatch between the referencing and referenced tables.

Implementing Entity and Referential Integrity Constraints

  • Constraints can be applied during table creation or added later via ALTER TABLE if they were originally omitted or lost during a database import.
Integrity Rule Application
  • Primary Keys: Defined to ensure entity integrity.
    • Syntax: ALTER TABLE tablename ADD PRIMARY KEY (columnname);
  • Foreign Keys: Defined to ensure referential integrity between tables.
    • Syntax: ALTER TABLE tablename ADD FOREIGN KEY (columnname) REFERENCES referenced_table(referenced_column);
  • Composite Keys: Specified by listing multiple attributes in parentheses: PRIMARY KEY (INV_NUMBER, LINE_NUMBER). The order of listing determines the indexing hierarchy.
Advanced Constraints
  • NOT NULL: Prevents null entries in a column.
  • UNIQUE: Ensures all entries in a column (or column group) are distinct.
  • DEFAULT: Assigns a value automatically if no entry is provided during an insert operation.
    • Example: DEFAULT '615' for an area code or DEFAULT NOW() for a timestamp.
  • CHECK: Validates that data meets a specific boolean condition before acceptance.
    • Example: CHECK (PART_PRICE >= 0) or CHECK (CUS_AREACODE IN ('615', '713', '931')).
  • Named Constraints: It is a best practice to name constraints using the CONSTRAINT keyword (e.g., CONSTRAINT CUS_UI1 UNIQUE...). This allows for easier identification when the constraint needs to be dropped later.

Referential Actions and Cascading

  • Referential integrity governs the relationship between "one" side (parent) and "many" side (child) tables.
  • Default Behavior: Deleting a record from the parent table is prohibited if active references exist in the child table.
Cascading Options
  • ON DELETE CASCADE: Automatically deletes child records when the corresponding parent record is deleted. Often recommended for weak entities.
  • ON UPDATE CASCADE: Automatically propagates changes to a primary key value in the parent table down to all referencing foreign keys in child tables.
  • RDBMS Variances:
    • Oracle: Does not support ON UPDATE CASCADE because it adheres to the principle that primary key values should be immutable. It does support ON DELETE CASCADE and SET NULL.
    • MySQL: Requires the InnoDB engine to enforce these constraints.
    • MS Access / SQL Server: Support cascading through graphical interfaces, but support at the SQL command line varies.

Deleting Tables from the Database

  • The command DROP TABLE tablename; permanently removes a table structure and its data.
  • Integrity Dependency: A table cannot be dropped if it resides on the "one" side of a relationship with active references. Attempting to do so results in a foreign key integrity violation error.
  • Procedural Order: To remove related tables, always drop the tables on the "many" side first before attempting to drop the table on the "one" side.