w3resource

PostgreSQL Create Table: Create a table to allow one of the columns of a table to contain only an auto-incremented value


11. 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.

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 SERIAL PRIMARY KEY, -- Defines an auto-incrementing integer column 'COUNTRY_ID' 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.
  • SERIAL is a PostgreSQL data type that automatically generates a unique integer for each row inserted into the table. It is commonly used for primary key columns that need to be unique identifiers for each row.
  • 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.
  • 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_NAME' and 'REGION_ID' columns cannot contain NULL values.

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 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.
Next: Write a SQL statement to create a table countries, including country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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://www.w3resource.com/postgresql-exercises/create-table/create-table-exercise-11.php