PostgreSQL Insert Record: Insert a record into the table to ensure that, the combination of two columns will be entered once in that table

8. Write a SQL statement to insert a record into the table countries to ensure that, at country_id and the region_id combination will be entered once in the table.

Sample Solution:

Code:

Here is the code to create a sample table countries:

-- This SQL statement creates a new table named 'countries' with specified columns and constraints.

CREATE TABLE countries ( 
    COUNTRY_ID integer NOT NULL,         -- Defines the COUNTRY_ID column as an integer type, which cannot be NULL
    COUNTRY_NAME varchar(40) NOT NULL,  -- Defines the COUNTRY_NAME column as a varchar type with a maximum length of 40 characters, which cannot be NULL
    REGION_ID integer NOT NULL,          -- Defines the REGION_ID column as an integer type, which cannot be NULL
    PRIMARY KEY (COUNTRY_ID, REGION_ID)  -- Defines a composite primary key constraint on the COUNTRY_ID and REGION_ID columns
);

Explanation:

• The CREATE TABLE statement is used to create a new table in the database.
• countries is the name of the new table being created.
• (COUNTRY_ID integer NOT NULL, COUNTRY_NAME varchar(40) NOT NULL, REGION_ID integer NOT NULL) specifies the columns of the table along with their data types and constraints.
• COUNTRY_ID, COUNTRY_NAME, and REGION_ID are defined as integer and varchar type columns with appropriate constraints to disallow NULL values.
• PRIMARY KEY (COUNTRY_ID, REGION_ID) defines a composite primary key constraint on the combination of the COUNTRY_ID and REGION_ID columns. This ensures that each combination of country and region is unique within the table.>

Now insert the value into the table countries :

INSERT INTO countries VALUES(501,'India',185);

Here is the command to see the list of inserting rows :

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       185
(1 row)

Now try to insert another row with the same combination of values for country_id and region_id, and see what happened:

postgres=# INSERT INTO kan VALUES(501,'Italy',185);
ERROR:  duplicate key value violates unique constraint "countries_pkey"
DETAIL:  Key (country_id, region_id)=(501, 185) already exists.

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

Previous: Write a SQL statement to insert one row in the jobs table to ensure that no duplicate values will be entered into the job_id column.
Next: Write a SQL statement to insert rows into the table countries in which the value of country_id column will be unique and auto incremented.