With a table created with this command:
CREATE TABLE people ( age INTEGER NOT NULL, name TEXT NOT NULL );
We can have columns that repeat the same value. The DBMS does not care.
But, we can force a column to have only unique values using the
UNIQUE key constraint:
DROP TABLE people; CREATE TABLE people ( age INTEGER NOT NULL, name TEXT NOT NULL UNIQUE );
Now if you try to add the ‘Flavio’ twice:
INSERT INTO people (age, name) VALUES (39, 'Flavio'); INSERT INTO people (age, name) VALUES (38, 'Flavio');
You’d get an error:
A primary key is a unique key that has another property: it’s the primary way we identify a row in the table.
What we usually do (but not always) is we use the
SERIAL type for the primary key, which means it’s a unique
INTEGER value that we assign to each record and we increment it automatically any time we add as new record.
DROP TABLE people; CREATE TABLE people ( id SERIAL PRIMARY KEY, age INTEGER NOT NULL, name TEXT NOT NULL );
So when we add an item we can omit its value, because the DBMS knows its value:
INSERT INTO people (age, name) VALUES (39, 'Flavio'); INSERT INTO people (age, name) VALUES (20, 'Roger');
id | age | name ---+-----+------ 1 | 39 | 'Flavio' 2 | 20 | 'Roger'
It’s important to define a primary key so:
- we always know we can uniquely identify a row in the table
- we can create relationships between tables, so a table can reference a row into another table
- we can have data integrity and referential integrity (something we’ll not talk about, but it’s important to note)
- and last but not least, it’s important for speed, so the database can figure out how to optimize your table
Lessons this unit:
|2:||Create a Postgres database|
|3:||Create a new table|
|4:||Add data to a table|
|5:||Query data with SELECT|
|6:||Delete data from the database|
|7:||Update data with UPDATE|
|9:||▶︎ Unique and primary keys|
|10:||Updating a table structure|
|12:||DEMO Using Vercel Postgres COMING SOON|