w3resource

PostgreSQL Create Table: Create a table to restrict the insertion of duplicate value for a specified column


8. Write a SQL statement to create a table named countries, including country_id, country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion.

Sample Solution:

Code:

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL,
UNIQUE(COUNTRY_ID)
);

This example can also be written like below.

Code:


CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL UNIQUE,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);

Output:

postgres=# CREATE TABLE IF NOT EXISTS countries (
postgres(# COUNTRY_ID varchar(2) NOT NULL,
postgres(# COUNTRY_NAME varchar(40) NOT NULL,
postgres(# REGION_ID decimal(10,0) NOT NULL,
postgres(# UNIQUE(COUNTRY_ID)
postgres(# );
CREATE TABLE

Here is the command to see the structure of the table :

postgres=# \d countries
           Table "public.countries"
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 country_id   | character varying(2)  | not null
 country_name | character varying(40) | not null
 region_id    | numeric(10,0)         | not null
Indexes:
    "countries_country_id_key" UNIQUE CONSTRAINT, btree (country_id)

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to create a table named countries, including country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table.
Next: Write a SQL statement to create a table named jobs, including job_id, job_title, min_salary and max_salary, and make sure that, the default value for job_title is blank and min_salary is 8000 and max_salary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.