DDL - Part 7 - HELP CONSTRAINT and ALTER TABLE

HELP CONSTRAINT command:

We can use HELP constraint command with name constraints to get information about the constraints.

When using the HELP constraint we qualify the constraint name with table name.

HELP CONSTRAINT TEST2.pk_2

Name	Type	Unique?	Index Id	Column Names
PK_2	PRIMARY KEY	Y	1	COLUMN1

Altering Table constraints using ALTER TABLE syntax

Adding constraints:

ALTER TABLE tablename
        ADD CONSTRAINT constrname CHECK
        ADD CONSTRAINT constrname UNIQUE
        ADD CONSTRAINT constrname PRIMARY KEY
        ADD CONSTRAINT constrname FOREIGN KEY

"CONSTRAINT constrname" is needed when we intent to name a constraint.

As we learned earlier constraint name can be named or unnamed.

Modifying existing constraints:

ALTER TABLE tablename MODIFY CONSTRAINT constrname;

Dropping constraints:

ALTER TABLE tablename DROP CONSTRAINT constrname;

Adding/Modifying/Dropping CHECK constraints:

1. Adding unnamed constraint: ALTER TABLE TEST2 ADD CHECK (column3 > 50);
    
2. Dropping unnamed constraint: ALTER TABLE TEST2 DROP  column3 CHECK;
    

3. Dropping all unnamed check constraints: ALTER TABLE TEST2 DROP  CHECK;

Note that with the above syntax only all unnamed constraints will be dropped. Named ones will not be dropped.

4. Adding named constraint: ALTER TABLE TEST2 ADD CONSTRAINT ch3 ChECK(column2 > 40);

5. Dropping named constraint: ALTER TABLE TEST2 DROP CONSTRAINT ch3 ;

We can add the keyword CHECK at the end of the statement to ensure that it’s the CHECK constraint that we are dropping.

Note: Only named constraints can be modified. For unnamed constraints we need to drop the constraint and recreate it.

This is the advantage of named constraints.

Also if the data in the table does not conform to the new check constraint then an error is returned.

6. Modifying the constraint : ALTER TABLE TEST2 modify constraint ch2 check(column3 > 50);

Adding/Dropping UNIQUE constraint:

Caution before adding UNIQUE constraint:
 

• Columns must be defined as NOT NULL before uniqueness constraint is applied to them.

• Data on the column must be unique , otherwise the constraint will be rejected

1. Adding unnamed UNIQUE constraints: ALTER TABLE TEST2 ADD UNIQUE(column1,column2);

2. Dropping unnamed UNIQUE constraints: Note that UNIQUE constraints are implemented as unique indexes- primary or secondary.

So to drop a UNIQUE index we need to drop the index using DROP INDEX syntax:

This is the only way to drop unnamed UNIQUE indexes

DROP INDEX(column1,column2) ON TEST2;

3. Adding named UNIQUE constraints:  ALTER TABLE TEST2 ADD CONSTRAINT CONT1 UNIQUE(column1,column2);

4. Dropping Named UNIQUE constraints : ALTER TABLE TEST2 DROP CONSTRAINT CONT1;

As we know that UNIQUE is always implemented as an index, we can also use the drop index command to drop a constraint.

DROP INDEX (column1,column2) on TEST2;

Adding / Dropping Primary Key Constraint:

Note that unlike Primary index , Primary key can be dropped and added.

There can be only 1 primary key per table.
 

Primary keys are implemented as unique secondary indexes or Unique primary indexes.

Primary key can be dropped by using the DROP CONSTRAINT syntax for named primary keys and drop index syntax for unnamed. However its very imp to note that if a primary key is implemented as Unique Primary index then it cannot be dropped.

1. Adding unnamed primary key: ALTER TABLE TEST2 ADD PRIMARY KEY(column1);

2. Dropping unnamed primary key : DROP INDEX (column1) ON TEST2;

 

Note that this technique of dropping the key can work only if the primary key has not been implemented as UPI.

3. Adding Named primary key constraint: ALTER TABLE TEST2 ADD CONSTRAINT pk1 PRIMARY KEY (column1);

4. Dropping Named primary key constraint : ALTER TABLE TEST3 DROP CONSTRAINT pk1;

This primary can also be dropped using the DROP INDEX syntax.

Adding / Dropping Foreign key Constraint :

Just like all other indexes Foreign key constraints can also be named or unnamed.

Rules for adding FK are:

1. The referenced column (column in the parent table ) must be defined as unique and not NULL

2. Referenced and Referencing columns must match in data type

3. There should be no values in the referencing table that are not present on the referenced table.

If such inconsistencies are found an error table is created with the unreferenced rows. Note that the alter table operation will look successful even though there are unreferenced values in the child table. The unreferenced rows will be left untouched in the child table. But Copies of these rows will be written to an error table which has same name as the child table with a numeric value appended to it. Very imp to note that Alter table statement will not return any error and will look successful.

So it’s the responsibility of the developer or DBA who alters the table to check for the existence of the error table and then fix those inconsistencies.

Example :

/* Create a parent table */

CREATE TABLE TEST_PARENT

(

column1 integer NOT NULL PRIMARY KEY

);

/* Create a child table */

CREATE TABLE TEST_CHILD

(

column2 integer NOT NULL PRIMARY KEY,

column3 integer

);

/* insert value 100 on the parent table */

insert into TEST_PARENT VALUES(100);

/* insert two rows to child table. One row has value 100 for column3 which is on the parent table, but other row has value 200 which is not on the parent table */

insert into TEST_CHILD VALUES(100,100);

insert into TEST_CHILD VALUES(120,200);

/*Here we define the Foreign key constraint */

ALTER TABLE TEST_CHILD ADD CONSTRAINT FK1 FOREIGN KEY(column3) REFERENCES TEST_PARENT(column1);

After this query is run the foreign key is created , but then another table  TEST_CHILD_0  is created which contains the 2nd row which does not have  matching row in the parent table. This table can be used to fix the referential inconsistencies.

Select * from TEST_CHILD_0;

Column2	column3
120	200

We can fix these inconsistencies using any of the 3 options:

1. Assigning a different value to the child row to have a value that already exists on the parent table.

UPDATE TEST_CHILD SET column3=100 WHERE column2=120;

2. Assigning a null value to the child row

UPDATE TEST_CHILD SET column3=NULL where column2 IN (Select column2 from TEST_CHILD_0);

3. Delete the record from the Child table

DELETE FROM TEST_CHILD WHERE column2 IN (Select column2 from TEST_CHILD_0);

1. Adding unnamed Foreign key : ALTER TABLE TEST2 ADD FOREIGN KEY(column1) REFERENCES TEST3(column2);

2. Dropping unnamed Foreign Key : ALTER TABLE TEST2 DROP FOREIGN KEY (column1) REFERENCES TEST3;

3. Adding a named Foreign key: ALTER TABLE TEST2 ADD CONSTRAINT fk2 FOREIGN KEY (col1,col2) REFERENCES TEST3(column1,column2)

4. Dropping Named constraint: ALTER TABLE TEST2 DROP CONSTRAINT fk2;