Adding Constraints to Tables

View All Scripts Login to Run Script

Script Name Adding Constraints to Tables
Description Creates a table and then alters the table definition to add check constraints
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1
Create a new table to manipulate the data and leave the original table untouched.
Create a new table using an existing table
```
CREATE TABLE JOBS_Temp AS SELECT * FROM HR.JOBS
```
```
Table created.
```

Statement 2

Display the information in the newly created table.

Display the table information

SELECT * FROM JOBS_Temp WHERE MIN_SALARY < 3000


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
PU_CLERK Purchasing Clerk 2500 5500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500

3 rows selected.

Statement 3
Adding a constraint to the minimum salary (min_salary) column. IMPORTANT NOTE: This step fails, as we are setting a constraint after inserting the values, which doesn't meet the required condition. This is a deliberate action to explain the constraint.
Add a constraint to the table
```
ALTER TABLE JOBS_Temp ADD CONSTRAINT chk_sal_min CHECK (MIN_SALARY >=2010)
```
```
ORA-02293: cannot validate (SQL_AXFPHFCOGWWGPCCDQBLNJZJYT.CHK_SAL_MIN) - check constraint violated 
```
More Details: https://docs.oracle.com/error-help/db/ora-02293
Statement 4
Update the column values where we added the constraint and the previous statement failed.
Update the table column where we added a constraint
```
UPDATE JOBS_Temp SET MIN_SALARY = 2300 WHERE MIN_SALARY < 2010
```
```
1 row(s) updated.
```
Statement 5
Adding the constraint one more time. This time, the step will succeed, after we updated the lower values to a higher value with the update statement.
Adding the constraint to the column
```
ALTER TABLE JOBS_Temp ADD CONSTRAINT chk_sal_min CHECK (MIN_SALARY >=2010)
```
```
Table altered.
```

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
PU_CLERK	Purchasing Clerk	2500	5500
ST_CLERK	Stock Clerk	2008	5000
SH_CLERK	Shipping Clerk	2500	5500

Statement 6

Display the information using a select statement.

Display the table information

SELECT * FROM JOBS_Temp WHERE MIN_SALARY < 3000


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
PU_CLERK Purchasing Clerk 2500 5500
ST_CLERK Stock Clerk 2300 5000
SH_CLERK Shipping Clerk 2500 5500

3 rows selected.

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
PU_CLERK	Purchasing Clerk	2500	5500
ST_CLERK	Stock Clerk	2300	5000
SH_CLERK	Shipping Clerk	2500	5500

Statement 7

Display the constraint to verify the newly added constraint has been added to the table.

Display the contraints in the table

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='JOBS_TEMP'


CONSTRAINT_NAME
SYS_C002709353
CHK_SAL_MIN

2 rows selected.

CONSTRAINT_NAME
SYS_C002709353
CHK_SAL_MIN

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database