Lecture 12--Create a database table
- [Instructor] In order to store data in a database, we need to create a table. The structure of a table is critical in creating a database that's easy to navigate and retrieve information from later. To create a new table in our custom database, I'll go ahead and expand the Databases node and find the database that we created called MyDatabase, then I'll expand that open and find the Tables folder. I'll right-click on it, point to New, and then click on Table. This will activate a second window where we can define the columns that'll make up the structure of our table. We simply need to give each column a name, define the type of data that it'll store, and then choose whether it'll be a required piece of information, which is the default state, or if we place a check in this box called Allow Nulls, we'll define an optional column that can be left blank when entering data.
So, let's create a table to store information about some customers. The first column is going to be an identification column. Every table should have a single column that could be used to uniquely identify each record. This is called the table's primary key. If I have two customers with the same first and last name, I want to be able to tell them apart. By creating a primary key, I can guarantee that I send invoices to the correct person. For the first named column, I'll go ahead and type in the name of CustomerID. This'll be used to store a numeric serial number that'll be attached to each customer as they get added into the database.
We can use whole numbers for this, so I'll choose the integer data type, int. Because I want to be certain that each customer has an ID value associated with them, I'll go ahead and uncheck this checkbox underneath Allow Nulls. The word null in database terminology simply means blank or empty. Next I want to make sure that SQL Server knows that this column will be storing the primary key data for the table, so on the Table Design toolbar at the top of the screen, I'll press this tiny key icon. Depending on the resolution of your monitor, this button might not fit on your toolbar.
If you're not seeing it, click on the downward pointing arrow on the far right to expand some additional options and you should find it there. That'll go ahead and set the primary key for the column and we can see that it has a new icon over here on the left-hand side. Finally, I don't really want to keep track of what ID numbers have been assigned to my customers. This is something that the server can take care of itself. To automatically assign IDs to new customers, come down to the Column Properties section at the bottom of the screen. Let's go ahead and drag this up a little bit so we can see more details. I'm going to scroll down until I find a property called Identity Specification, then I'll press on the arrow to the left side of it to twirl it open.
We can see that it contains some sub-properties as well. I'm going to change the Is Identity property from No to Yes by clicking on it and then using the drop-down menu to choose Yes. Then I'll change a couple of the other properties down below. Increment means that each new customer that gets added will have an ID that is one more than the previous ID that was issued, and the Seed value is simply the number that the first customer will receive. Let's go ahead and start off the table with ID number 1,000 instead of one. By setting the identity specification to Yes and allowing SQL Server to auto-assign ID values, I can ensure that each new customer gets a unique number without me having to micromanage the process.
So, that's our first column and honestly, that's the most complicated one. Let's go back up to the top and add in our second column. I'll click below CustomerID and the second column is going to be called FirstName. Note that as I'm entering in column names, I'm not using any spaces and capitalizing each successive word. This is a convention called camel casing. Now, this isn't a requirement, you can type in these values however you want, but it's important to be consistent with your names so that the database is easier to manage. Since I'm pretty sure that my customers won't all magically have the same number of characters in their name, I'll choose the varchar data type and set it to 25 characters.
I can't imagine that I'll have any customers with more than 25 letters in their name. I'll do the same thing with the LastName column, but I'll set the data type to a varchar of 50 characters. For both of these name columns, I want to be sure that I'm capturing this data, so I'll go ahead and uncheck the Allow Nulls checkboxes. As a final piece of information, let's go ahead and store our customer's birthdays so that we can send them a coupon on their special day. I'll come down to the fourth column and I'll type in the name BirthDate.
The data type for this will simply be date. Now, since not all of my customers will want to share this information with me, I'll go ahead and allow this field to be left blank by leaving the check mark here in the Allow Nulls box. To save the table's design, go ahead and press the save icon on the standard toolbar or simply try and close the tab. You'll be prompted to save the table and I'll give it the name Customers. Now we can go ahead and close this tab here and return back to our database. I'll expand the Tables folder here and we should see our new table down here.
It'll automatically get the prefix dbo, which stands for database owner, and then the name of the table is after that period. Now, if you're not seeing it here, go ahead and right-click on Tables and choose Refresh. To add data to this table, go ahead and right-click on it and choose Edit Top 200 Rows. That'll open up the table so we can enter in the details for a few of our customers. Remember that the customer ID is going to be automatically generated by SQL Server, so we'll go ahead and skip that field. I'll go ahead and click my cursor into the FirstName column.
The first name for our first customer is going to be Suzanne. I'll press the Tab key to move my cursor over to the LastName field. Notice that as you start typing, you'll see this red exclamation icon that indicates that the data has not yet been saved to our database. We'll go ahead and address this in just a moment. We'll type in the last name of Jones, I'll press Tab again to go over to the BirthDate column, and we'll enter in a date of 1/1/1999. When I press Enter, my cursor goes down to the next record below it and you'll notice that those red exclamation icons disappear.
That indicates that this data has now been saved into the database. And that's how you create data inside of SQL Server 2016. Using the Design screen, you'll give each column a name and choose a type of data that it'll store. You can choose whether the information is required or not using the Allow Nulls checkbox and set some additional properties for the column with the options below. And if you ever need to get back to or modify an existing table, go ahead and close this tab here, just simply right-click on the table and choose Design. That'll get you back into the Design window where you can make any changes that you need to to the table's design
▶️ DTube
▶️ IPFS