SQL: Creating Databases and Tables - Create Table Code

CREATE TABLE

The CREATE TABLE function creates a table in a database.

Postgre SQL Column Constraints

  • NOT NULL - the value of the column cannot be NULL

  • UNIQUE - the value of the column must be unique across the whole table

  • Primary Key - this constraint is the combination of NOT Null and UNIQUE constraints

  • CHECK - enables to check a condition when you insert or update data

  • REFERENCES - constrains the value of the column that exists in a column in another table (Define the foreign key)

PostgreSQL Table Constraints

  • Unique (column_list) - to force the value stored in the columns listed inside the parentheses to be unique

  • Primary Key (column_list) - to define the primary key that consists of multiple column

  • Check (condition) - to check a condition when inserting or updating data

  • References - to constrain the value store din the column that must exist in a column in another table

Syntax:

-- CREATE TABLE
CREATE TABLE table_name
(column_name TYPE column_constraint,
table_constraint)
INHERITS existing_table_name;

Example: Account table

-- Account table
CREATE TABLE account(
user_id serial PRIMARY KEY,
username VARCHAR (50) UNIQUE NOT NULL,
password VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);
## NULL

Example: Role table

-- Role table.
CREATE TABLE role(
role_id serial PRIMARY KEY,
role_name VARCHAR (255) UNIQUE NOT NULL
);
## NULL

Example: Account-role table

-- Account-role table
CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
## NULL

Challenge

Problem : A customer’s first name, last name,email,sign-up date, and number of minutes spent on the dvd rental site. You should also have some sort of id tracker for them.

-- Role table.
CREATE TABLE potential_leads(
user_id serial PRIMARY KEY, 
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
sign_up_date TIMESTAMP NOT NULL,
minutes_spent_on_dvd_rental_site integer NOT NULL
);
## NULL