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;

DDL - Part 5 - Table Level Constraints

TABLE level constraints:

Earlier we saw column level constraints. We can also specify  constraints at table level .

Table level constraints are specified after the column definitions.

Like column level constraints they can also be named or unnamed.

Major difference between column level and table level constraints is that with column level constraints we cannot specify multi column constraints. .ie we cannot define a PK that comprises of multiple columns  using column level constraints. Here we need to use table level constraints as shown below:

CREATE TABLE tbl1
        (col1        INT   NOT NULL,
        col2        INT   NOT NULL,
        col3        INT   NOT NULL,
        col4        INT   NOT NULL,
        col5        INT,
        col6        INT,
CONSTRAINT  primary_1
        PRIMARY KEY       (col1,col2),
CONSTRAINT  unique_1   
        UNIQUE            (col3,col4),
CONSTRAINT  check_1     
        CHECK             (col2 > 0 OR col4 > 0),
CONSTRAINT  refer_1      
        FOREIGN KEY       (col5,col6)
       REFERENCES parent_1 (colA,colB)
        );

Note that we don’t use the word FOREIGN KEY while defining constraint at column level. Here we use it to identify which columns form the FOREIGN KEY.

Note following rules while creating table relations using REFERENCES.

1.    Table that references. .i.e. one with FOREIGN KEY is the child table.
 

2.    Table that is referenced is the parent table.
 

3.    Up to 64 foreign keys may be defined for a child table.
 

4.    Up to 64 references may reference a parent table.
 

5.    The table creator must have the REFERENCES privilege on referenced table.

6.    The referenced column must be defined as NOT NULL.
 

7.    The referenced column must be defined unique using one of the following methods:
 

·   USI

·   UPI

·   PRIMARY KEY constraint

·   UNIQUE constraint

(note that referenced column need not always be a PRIMARY KEY. Basic requirement is that the column should be NOT NULL and UNIQUE.)

Example:

CREATE TABLE MY_TEST_TABLES.TEST

(

column1 integer

);

IN table TEST the column column1 is not defined as NOT NULL and is not made unique. Now If we try to create a below table if will error out.

CREATE TABLE MY_TEST_TABLES.TEST2

(

column1 integer NOT NULL CONSTRAINT pk_2 PRIMARY KEY,

column4 integer constraint fk_1 references test(column1)

);

Error: CREATE TABLE Failed. 3977:  The Parent Key is not unique or not valid.

The query fails because column1 on test table is not defined as NOT NULL and UNIQUE.

DDL - Part 4 - Column Level Constraints

Column Level Constraints:

ANSI permits constraints to be placed at column level.

Constraints can be named or unnamed. Using named constraints allows us to get HELP on the constraints.

4 types of column level attributes are:
 

PRIMARY KEY:	Used to guarantee uniqueness. The column should be defined as NOT NULL.
UNIQUE :	Used to guarantee uniqueness of a column. The column should be defined as NOT NULL.
CHECK :	Allows us to specify the range of value or value constraints permissible in the column.
REFERENCES:	Requires values to be reference checked before being allowed in the column.

Example 1:

Following query will fail because for making a column PRIMARY KEY it has to be defined as NOT NUL

CREATE TABLE EDW_RESTORE_TABLE.TEST

(

column1 integer PRIMARY KEY

);

Error: CREATE TABLE Failed. 3706:  Syntax error: PRIMARY KEY Column(s) must be NOT NULL. 

Correct DDL would be as follows:

CREATE TABLE MY_TEST_TABLES.TEST

(

column1 integer NOT NULL PRIMARY KEY

);

Note that here the constraint is not named. We should make a practice to add names to constraints.

DATABASE MY_TEST_TABLES

CREATE TABLE TEST

(

column1 integer NOT NULL CONSTRAINT PK_1 PRIMARY KEY

);

Above we specified a constraint at column level. However internally it always gets implemented at table level.

We can confirm this by doing a SHOW TABLE command.

Output of SHOW table:

show table test

CREATE SET TABLE MY_TEST_TABLES.test ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      column1 INTEGER NOT NULL,

CONSTRAINT PK_1 PRIMARY KEY ( column1 ))

;

Example 2:

CREATE TABLE MY_TEST_TABLES.TEST2

(

--All the constraints are named.

--To name a constraint we use the keywword CONSTRAINT followed by the name we provide

--followed by the actual cnstraint definition

column1 integer NOT NULL CONSTRAINT pk_2 PRIMARY KEY,

column2 integer NOT NULL CONSTRAINT Uk_2 UNIQUE,

--note that PRIMARY KE AND UNIQUE bot need columns to be NOT NULL

column3 integer CONSTRAINT ch2 CHECK(column3 > 100),

column4 integer constraint fk_1 references test1(column1)

);

SHOW TABLE TEST2;

CREATE SET TABLE MY_TEST_TABLES.TEST2 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      column1 INTEGER NOT NULL,

      column2 INTEGER NOT NULL,

      column3 INTEGER,

      column4 INTEGER,

      CONSTRAINT ch2 CHECK ( column3 >  100  ),

      CONSTRAINT pk_2 PRIMARY KEY ( column1 ),

      CONSTRAINT Uk_2 UNIQUE ( column2 ),

       CONSTRAINT fk_1 FOREIGN KEY ( column4 ) REFERENCES MY_TEST_TABLES.TEST1 ( COLUMN1 ))

;

All constraints get implemented at table level.

HELP INDEX MY_TEST_TABLES.TEST2

Unique?	Primary or Secondary?	Column Names	Index Id	Approximate Count	Index Name	Ordered or Partitioned?
Y	P	Column1	1	0.00	pk_2	H
Y	S	Column2	4	0.00	Uk_2	H

Following are the points to note:

1.    The PRIMARY KEY is implemented as unique primary index.
 

2.    The UNIQUE constraint is implemented as a unique secondary index.
 

3.    The CHECK constraint is implemented at the table level.
 

4.    The REFERENCES constraint is implemented as a FOREIGN KEY at the table level.
 

5.    Because the table is created in Teradata mode, it is a SET table.
 

6.    The character field is implemented with a NOT CASESPECIFIC attribute.