How to create unique key and primary key in SQL database
Use the table created by this command:
CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL
);
We can insert an item multiple times.
In particular, we can have columns that repeat the same value.
We can useUNIQUE
Key constraints:
CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL UNIQUE
);
Now, if you try to add "Flavio" twice:
INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (20, 'Flavio');
You will get an error:
ERROR: duplicate key value violates unique constraint "people_name_key"
DETAIL: Key (name)=(Flavio) already exists.A primary key is a unique key that has another property: it’s the primary way we identify a row in the table.
CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL PRIMARY KEY
);
The primary key can be an email in a list of users, for example.
The primary key can be a unique id
that we assign to each record automatically.
Whatever that value is, we know we can use it to reference a row in the table.
More database tutorials:
- The MongoDB basics tutorial
- How MongoDB is different from a SQL database
- Information systems, data and information
- Data models
- Introduction to the ER Data Model
- The Relational Model
- Relational Algebra
- Relational Databases
- What is a Database? And a DBMS?
- Introduction to SQL
- How to install PostgreSQL on macOS
- Do you always need a database for your app?
- How to install SQLite on macOS
- Introduction to PostgreSQL
- PostgreSQL User Permissions
- How to list all users in PostgreSQL
- How to switch database using PostgreSQL
- How to list all databases using PostgreSQL
- How to list tables in the current database using PostgreSQL
- How to install MySQL on macOS
- Creating a user on MySQL
- PostgreSQL vs MySQL, a comparison
- SQLite User Permissions
- MySQL User Permissions
- SQL, creating a table
- SQL, adding data to a table
- SQL, how to use SELECT
- SQL, Handling empty cells
- SQL, Unique and Primary keys
- SQL, how to update data
- SQL, how to update a table structure
- SQL, how to delete data and tables
- SQL Joins
- SQL Views
- How to insert multiple items at once in a MongoDB collection
- How to remove all items from a MongoDB collection