Creating a Table

Here's some basic code to create a table:

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

This code creates a table like this:

With the names and types of each column listed along the top.

Creating this table In DB-FIDDLE:

Creating this In mysql cli:

This means if we were to add an entry or 'row' to our table, each entry (or new user) would have an ID, a NAME, and an EMAIL.

Let's break it down bit by bit-

CREATE TABLE Users ();

Commands in SQL are like our keywords in Javascript. Things like "for" and "const" and "function". They're reserved words we use to let the machine know what we're trying to accomplish. Typically- you'll see all of the commands in SQL used in all "CAPS".

so just like how

javascript;
function myFunction() {}

Means we want to create our own function named "myFunction"-

CREATE TABLE Users (
);

Means we want to create a new table called "Users". Everything we write inside of this call are different 'fields' or 'columns' for that table.

For instance:

CREATE TABLE Users (
    id INT
);

Means that each 'user' in our 'Users' table will have an 'id' field of type 'INT' (integer).

Each 'field' in our tables need at least 2 things.

1. A name ('id' in this case)
2. A type ('INT' in this case)

Names are just like keys in a regular javascript object.

javascript;
const someUser = {
  id: 12,
};

And it follows some similar rules. We can't have a duplicate field name just like we can't have a duplicate key name:

javascript;
const someUser = {
  id: 12,
  id: 15,
};

They have to be unique.

The 'type' that comes after the name is similar to Typescript. We need to 'type' our fields so that the database knows what kind of data is being held there. This way it can optimize searching/indexing as well as optimize space being used.

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT
);

These next two commands are special. They're called "CONSTRAINTS".

What comes after the 'type' in SQL when defining a table is known as a 'CONSTRAINT'. These apply special rules to that column or 'field'.

"PRIMARY KEY" means that this is the unique identifier for each entry (or row) of a table. No two users can have the same "id". This is how our database will tell Users apart- and will be how it indexes results for searching/querying.

"AUTO_INCREMENT" does what it says. It will automatically add 1 to each id as the Users get added to the table.

So the first User that gets added to the table will have an ID of "1" and the next user will automatically have an ID of "2" etc...

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Once again, we've added a new 'field' or 'column' to our schema. We now have a "name" of type "VARCHAR(50)".

"VARCHAR" you can think of like 'string' in typescript.

But what's the 50? Well that's the LENGTH that the string can be. So we're telling the database the name can only be 50 characters long at most.

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

Same thing here: We now added 'email' to our fields, as a 'string' with a maximum length of 100 characters.

But what about 'UNIQUE'? Well that's another 'CONSTRAINT'. Remember anything after the type is a 'CONSTRAINT' that applies special rules to that field.

So in this case, we've added a 'rule' that 'email' has to be unique. Meaning you cannot add 2 users that share the same Email. The database will actually prevent you from doing so because of this 'rule' we added.

In the diagram above, you can see that the database won't accept anything that breaks our constraints. These are hard rules for our database.

However, below we can see that if we follow our rules, things work as expected!

0 Comments

Top comments

"Please login to view comments"

Join the Conversation!

Subscribing gives you access to the comments so you can share your ideas, ask questions, and connect with others.

Upgrade your account