PostgreSQL Create Table: Create a table to restrict one of the columns to contain any duplicate values
10. Write a SQL statement to create a table named countries, including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data 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 UNIQUE PRIMARY KEY, -- Defines a column 'COUNTRY_ID' to store country IDs as strings with a maximum length of 2 characters. The column cannot contain NULL values, and each value must be unique across all rows. Additionally, it is set as the primary key for the table.
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.
);
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 'COUNTRY_ID', 'COUNTRY_NAME', and 'REGION_ID' columns cannot contain NULL values.
- The UNIQUE constraint ensures that each value in the 'COUNTRY_ID' column must be unique across all rows in the table.
- The PRIMARY KEY constraint sets the 'COUNTRY_ID' column as the primary key for the table, ensuring uniqueness and providing a unique identifier for each row in the table.
The above statement can be written like below.
Code:
CREATE TABLE countries (
COUNTRY_ID varchar(2) NOT NULL DEFAULT '',
COUNTRY_NAME varchar(40) DEFAULT NULL,
REGION_ID decimal(10,0) DEFAULT NULL,
PRIMARY KEY (COUNTRY_ID));
Output:
postgres=# postgres=# CREATE TABLE IF NOT EXISTS countries ( postgres(# COUNTRY_ID varchar(2) NOT NULL UNIQUE PRIMARY KEY, postgres(# COUNTRY_NAME varchar(40) NOT NULL, postgres(# REGION_ID decimal(10,0) NOT NULL postgres(# ); CREATE TABLE
Here is the command to see the structure of the created 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_pkey" PRIMARY KEY, 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 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.
Next: Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the column country_id will be unique and store an auto-incremented value.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/postgresql-exercises/create-table/create-table-exercise-10.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics