How to Add Primary Key in BigQuery

How do I add a primary key to a table in BigQuery?

ALTER TABLE … ADD PRIMARY KEY defines a non-enforced uniqueness constraint on one or more columns.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why add a primary key in BigQuery?

BigQuery now lets you declare PRIMARY KEY constraints to signal that rows are unique. Though NOT ENFORCED, the flag improves data modeling, downstream tool compatibility, and query documentation.

What is the basic ALTER TABLE syntax?

Use ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_list) NOT ENFORCED; The NOT ENFORCED clause is mandatory today.

How do I add a primary key to an existing table?

Run ALTER TABLE Customers ADD CONSTRAINT pk_customers PRIMARY KEY(id) NOT ENFORCED; This tells BigQuery each id in Customers is unique.

Can I create a primary key during table creation?

Yes. CREATE TABLE Orders(id INT64, customer_id INT64, order_date DATE, total_amount NUMERIC, CONSTRAINT pk_orders PRIMARY KEY(id) NOT ENFORCED); declares the key at creation time.

How do composite keys work?

List multiple columns. Example: ALTER TABLE OrderItems ADD CONSTRAINT pk_orderitems PRIMARY KEY(order_id, product_id) NOT ENFORCED; ensures each product appears only once per order.

Does BigQuery validate the primary key?

No. The engine does not prevent duplicates yet. You must manage data quality via ETL checks or query audits.

What are best practices?

1) Add keys in staging first to catch issues. 2) Name constraints pk_

. 3) Document composite keys in comments. 4) Periodically verify uniqueness with SELECT COUNT(*) vs COUNT(DISTINCT …).

How to drop or change a primary key?

Use ALTER TABLE Customers DROP CONSTRAINT pk_customers; then add a new one if needed.

Performance considerations?

Because constraints are not enforced, they do not impact read or write speed. They solely add metadata. Future enforcement may introduce checks.

Example: enforce uniqueness via job?

SELECT id, COUNT(*) FROM Customers GROUP BY id HAVING COUNT(*) > 1; returns duplicates you should fix before trusting the key.

Need code snippets?

See the syntax and ecommerce example below.

Why How to Add Primary Key in BigQuery is important

How to Add Primary Key in BigQuery Example Usage


-- Ensure each product appears once per order
ALTER TABLE OrderItems
ADD CONSTRAINT pk_orderitems PRIMARY KEY(order_id, product_id) NOT ENFORCED;

How to Add Primary Key in BigQuery Syntax


-- Add primary key to existing table
ALTER TABLE Customers
ADD CONSTRAINT pk_customers PRIMARY KEY(id) NOT ENFORCED;

-- Create table with primary key
CREATE TABLE Orders (
  id INT64,
  customer_id INT64,
  order_date DATE,
  total_amount NUMERIC,
  CONSTRAINT pk_orders PRIMARY KEY(id) NOT ENFORCED
);

-- Composite primary key
ALTER TABLE OrderItems
ADD CONSTRAINT pk_orderitems PRIMARY KEY(order_id, product_id) NOT ENFORCED;

Common Mistakes

Using ALTER TABLE … PRIMARY KEY without NOT ENFORCED. BigQuery requires NOT ENFORCED; omitting it raises a syntax error. Always append NOT ENFORCED.
Assuming BigQuery blocks duplicates after adding a primary key. The constraint is informational only; duplicates can still be inserted. Use queries or ETL checks to enforce.