Tutorial: Adding Constraints to Existing Tables

To add constraints to existing tables, use either SQL Developer tools or the DDL statement ALTER TABLE. This topic shows how to use both of these ways to add constraints to the tables created in "Creating Tables".

This tutorial has several procedures. The first procedure (immediately after this paragraph) uses the Edit Table tool to add a Not Null constraint to the NAMES column of the PERFORMANCE_PARTS table. The remaining procedures show how to use other tools to add constraints; however, you could add the same constraints using the Edit Table tool.

To add a Not Null constraint using the Edit Table tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Tables.

   A list of tables appears.

3. Right-click PERFORMANCE_PARTS.

   A list of choices appears.

4. Click Edit.

   The Edit Table window opens. By default, Columns is selected, the columns of the PERFORMANCE_PARTS table are listed, the column PERFORMANCE_ID is selected, and its properties are listed.

5. Click the column NAME.

   The properties of the column NAME appear. The property "Cannot be NULL" is deselected.

6. Select Cannot be NULL.

7. Click OK.

   The Not Null constraint is added to the NAME column of the PERFORMANCE_PARTS table.

The following procedure uses the ALTER TABLE statement to add a Not Null constraint to the WEIGHT column of the PERFORMANCE_PARTS table.

To add a Not Null constraint using the ALTER TABLE statement:

1. Click the icon SQL Worksheet.

   The SQL Worksheet pane appears.

2. In the SQL Worksheet pane, type this statement:

   ALTER TABLE PERFORMANCE_PARTS
   MODIFY WEIGHT NOT NULL;
   

3. Click the icon Execute Statement.

   The statement runs, adding the Not Null constraint to the WEIGHT column of the PERFORMANCE_PARTS table.

The following procedure uses the Add Unique tool to add a Unique constraint to the SCORES table.

To add a Unique constraint using the Add Unique tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Tables.

   A list of tables appears.

3. Right-click SCORES.

   A list of choices appears.

4. Click Constraint.

   A list of choices appears.

5. Click Add Unique.

   The Add Unique window opens.

6. For Constraint Name, enter SCORES_EVAL_PERF_UNIQUE.

7. For Column 1, select EVALUATION_ID from the drop-down menu.

8. For Column 2, select PERFORMANCE_ID from the drop-down menu.

9. Click Apply.

   The Confirmation window opens.

10. Click OK.

    A unique constraint named SCORES_EVAL_PERF_UNIQUE is added to the SCORES table.

The following procedure uses the Add Primary Key tool to add a Primary Key constraint to the PERFORMANCE_ID column of the PERFORMANCE_PARTS table.

To add a Primary Key constraint using the Add Primary Key tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Tables.

   A list of tables appears.

3. Right-click PERFORMANCE_PARTS.

   A list of choices appears.

4. Click Constraint.

   A list of choices appears.

5. Click Add Primary Key.

   The Add Primary Key window opens.

6. For Primary Key Name, enter PERF_PERF_ID_PK.

7. For Column 1, select PERFORMANCE_ID from the drop-down menu.

8. Click Apply.

   The Confirmation window opens.

9. Click OK.

   A primary key constraint named PERF_PERF_ID_PK is added to the PERFORMANCE_ID column of the PERFORMANCE_PARTS table.

The following procedure uses the ALTER TABLE statement to add a Primary Key constraint to the EVALUATION_ID column of the EVALUATIONS table.

To add a Primary Key constraint using the ALTER TABLE statement:

1. Click the icon SQL Worksheet.

   The SQL Worksheet pane appears. Under "Enter SQL Statement:" is a field where you can enter a SQL statement.

2. In the SQL Worksheet pane, type this statement:

   ALTER TABLE EVALUATIONS
   ADD CONSTRAINT EVAL_EVAL_ID_PK PRIMARY KEY (EVALUATION_ID);
   

3. Click the icon Execute Statement.

   The statement runs, adding the Primary Key constraint to the EVALUATION_ID column of the EVALUATIONS table.

The following procedure uses the Add Foreign Key tool to add two Foreign Key constraints to the SCORES table.

To add two Foreign Key constraints using the Add Foreign Key tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Tables.

   A list of tables appears.

3. Right-click SCORES.

   A list of choices appears.

4. Click Constraint.

   A list of choices appears.

5. Click Add Foreign Key.

   The Add Foreign Key window opens.

6. For Foreign Key Name, enter SCORES_EVAL_FK.

7. For Column Name, select EVALUATION_ID from the drop-down menu.

8. For Reference Table Name, select EVALUATIONS from the drop-down menu.

9. For Referencing Column, select EVALUATION_ID from the drop-down menu.

10. Click Apply.

    The Confirmation window opens.

11. Click OK.

    A foreign key constraint named SCORES_EVAL_FK is added to the EVALUTION_ID column of the SCORES table, referencing the EVALUTION_ID column of the EVALUATIONS table.

    The following steps add another foreign key constraint to the SCORES table.

12. Right-click SCORES.

    A list of tables appears.

13. Click Constraint.

    A list of choices appears.

14. Click Add Foreign Key.

    The Add Foreign Key window opens.

15. For Foreign Key Name, enter SCORES_PERF_FK.

16. For Column Name, select PERFORMANCE_ID from the drop-down menu.

17. For Reference Table Name, select PERFORMANCE_PARTS from the drop-down menu.

18. For Referencing Column, select PERFORMANCE_ID from the drop-down menu.

19. Click Apply.

    The Confirmation window opens.

20. Click OK.

    A foreign key constraint named SCORES_PERF_FK is added to the EVALUTION_ID column of the SCORES table, referencing the EVALUTION_ID column of the EVALUATIONS table.

The following procedure uses the ALTER TABLE statement to add a Foreign Key constraint to the EMPLOYEE_ID column of the EVALUATIONS table, referencing the EMPLOYEE_ID column of the EMPLOYEES table.

To add a Foreign Key constraint using the ALTER TABLE statement:

1. Click the icon SQL Worksheet.

   The SQL Worksheet pane appears. Under "Enter SQL Statement:" is a field where you can enter a SQL statement.

2. In the SQL Worksheet pane, type this statement:

   ALTER TABLE EVALUATIONS
   ADD CONSTRAINT EVAL_EMP_ID_FK FOREIGN KEY (EMPLOYEE_ID)
   REFERENCES EMPLOYEES (EMPLOYEE_ID);
   

3. Click the icon Execute Statement.

   The statement runs, adding the Foreign Key constraint to the EMPLOYEE_ID column of the EVALUATIONS table, referencing the EMPLOYEE_ID column of the EMPLOYEES table.

The following procedure uses the Add Check tool to add a Check constraint to the SCORES table.

To add a Check constraint using the Add Check tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Tables.

   A list of tables appears.

3. Right-click SCORES.

   A list of choices appears.

4. Click Constraint.

   A list of choices appears.

5. Click Add Check.

   The Add Check window opens.

6. For Constraint Name, enter SCORE_VALID.

7. For Check Condition, enter score >= 0 and score <= 9.

8. For Status, accept the default, ENABLE.

9. Click Apply.

   The Confirmation window opens.

10. Click OK.

    A Check constraint named SCORE_VALID is added to the SCORES table.