How to create tables in Google BigQuery & PostgreSQL DB and what are keys and constraints?

Question

How to create tables in Google BigQuery & PostgreSQL DB and what are the constraints we can use in BigQuery but not in postgreSQL?

Answer 1

CREATE TABLE "TABLE_NAME" ( "Column 1" "data type for column 1" [column 1 constraints(s)], "column 2" "data type for column 2" [column 2 constraints(s)], ... "Column n"
[tabe constraint(s)] );

Table name will flow the <project_name>.<DB_Name>.<table_name> syntex

---

NOTE: IN GOOGLE BIG QUERY WE DO NOT HAVE CONSTRAINTS LIKE PRIMARY KEY, FOREIGN KEY, AND UNIQUE KEY, BUT IN PostgreSQL WE CAN USE THEM.

---

Constraints:

• NOT NULL Constraint: Ensures that a column cannot have a NULL value.

• DEFAULT Constraint: Provides a default value for a column when none is specified.

• UNIQUE Constraint: Make sure that all values in a column are different.

• CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.

• Primary Key Constraint: Used to uniquely identify a row in the table.

• Foreign Key Constraint: Used to ensure referential integrity of the data.

Keys:

• A primary key is used to uniquely identify each row in a table.
• A primary key can consist of one or more columns on a table.
• When multiple columns are used as a primary key, they are called composite keys.
• A foreign key is a column (or columns) that references a column (most often the primary key) of another table.
• The purpose of the foreign key is to ensure the referential integrity of the data.

create table Customer_tbl( cust_id int primary key identity, first_name varchar, last_name varchar, age int, email_id varchar, mbile_num varchar);