Let's add some more rules to our nearly completed User table.

We want to make sure we always have an email and name- so let's add a rule to our table. We can enforce that the values aren't empty by adding 'NOT NULL' to columns that we want to require a value.

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

Excellent. So let's review what we did here.

We used a command in SQL called "CREATE TABLE" to make a new table in our Database. Tables will hold our data- and when we make tables we need to specify what columns there are and the types of data in each column.

Each column we write inside of 'CREATE TABLE' has three basic parts. The name, the type, and the constraints.

Each column can have multiple constraints-

This is how we build out complex rules and automations for our data.

Let's add an automation now. Let's add a timestamp field to our Users table that will automatically create the timestamp for us when a user is created.

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);

We've added the createdAt timestamp to our table. Using the 'DEFAULT' constraint- we have made it so that whenever a record gets created, we set this 'createdAt' value to the current time (the time when it's created).

Using constraints give us a lot of powerful tools to keep data like timestamps or unique id's organized and controlled. With contraints like "AUTO_INCREMENT" and 'DEFAULT CURRENT_TIMESTAMP' we let our Database handle all of the logic for keeping track of this information- and we can just reap the benefits.

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