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:

-- This SQL statement creates a new table named 'countries' if it does not already exist,
-- defining the structure and constraints for the table columns.

CREATE TABLE IF NOT EXISTS countries ( 
    COUNTRY_ID varchar(2) NOT NULL, -- Defines a column 'COUNTRY_ID' to store country IDs as strings with a maximum length of 2 characters. The column cannot contain NULL values.
    COUNTRY_NAME varchar(40) NOT NULL, -- Defines a column 'COUNTRY_NAME' to store country names as strings with a maximum length of 40 characters. The column cannot contain NULL values.
    REGION_ID decimal(10,0) NOT NULL, -- Defines a column 'REGION_ID' to store region IDs as decimal numbers with a precision of 10 digits and no decimal places. The column cannot contain NULL values.
UNIQUE(COUNTRY_ID) -- Defines a unique constraint on the 'COUNTRY_ID' column, ensuring that each value in this column is unique across all rows.
);

Explanation:

  • The CREATE TABLE IF NOT EXISTS statement creates a new table only if it does not already exist in the database.
  • The table is named 'countries'.
  • Each column definition specifies the column name, data type, and optional constraints.
  • varchar(2) indicates a variable-length character string with a maximum length of 2 characters for the 'COUNTRY_ID' column.
  • varchar(40) indicates a variable-length character string with a maximum length of 40 characters for the 'COUNTRY_NAME' column.
  • decimal(10,0) indicates a decimal number with a precision of 10 digits and no decimal places for the 'REGION_ID' column.
  • The NOT NULL constraint ensures that none of the columns ('COUNTRY_ID', 'COUNTRY_NAME', and 'REGION_ID') can contain NULL values.
  • The UNIQUE constraint ensures that each value in the 'COUNTRY_ID' column must be unique across all rows in the table.

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.