Let us understand how we can add UNIQUE constraint to job_title in jobs table.
- We can either specify unique constraint using ALTER TABLE command or even while creating table.
- Once we specify unique constraint on job_title, we can either have unique job titles or null in that column. Uniqueness will be enforced only on non empty or not null job titles.
Unique Constraint - ALTER TABLE
Here is how we can use ALTER TABLE command to add unique constraint. However if there are duplicate job titles this will fail.
ALTER TABLE jobs ADD CONSTRAINT jobs_uk1 UNIQUE (job_title);
Unique Constraint - CREATE TABLE
We can add unique constraints using all the 3 approaches which we have seen while adding Primary Key.
- Along with column
- After all columns
- Using alter table after creating table
- In this case we will use the first approach.
DROP TABLE jobs; CREATE TABLE jobs ( job_id INTEGER GENERATED BY DEFAULT AS IDENTITY START WITH 1 INCREMENT BY 1 CONSTRAINT jobs_pk PRIMARY KEY, job_title VARCHAR2(30) CONSTRAINT jobs_uk1 UNIQUE, min_salary NUMBER, max_salary NUMBER );
Inserting data into jobs
All these 3 insert statements will work with out any issues.
INSERT INTO jobs (job_title, min_salary, max_salary) VALUES ('PRESIDENT', 1000, NULL); INSERT INTO jobs (min_salary, max_salary) VALUES (1500, NULL); INSERT INTO jobs (min_salary, max_salary) VALUES (1500, 1000);
However this insert statement will fail, as this violates unique constraint.
INSERT INTO jobs (job_title, min_salary, max_salary) VALUES ('PRESIDENT', 1000, NULL);