Introduction to Tables and Constraints

Video Transcription

Welcome everyone. In this video, we’re going to take our first look into some sequels, specifically t SQL, which is Microsoft’s implementation of the SQL standard.So to start off, we’ll talk a little bit about basic table structure. We’ve talked up already in a previous lecture about general table structure, but not necessarily how we actually create that in our database. We’ll also do a basic introduction to constraints today, which includes the not null constraint, primary keys, unique keys, foreign keys, check constraints and default constraints. These are by no means all of the constraints that are possible to use in SQL, as well as SQL Server, which is the database system that we’ll be using. But this should get you a good introduction into them. And we’ll be covering and using these throughout the course.

So as we noted before, tables are a physical form of a relation. Remember, relations are collections of attributes, tuples, and all of that, basically, what we saw with the university data, so the table is the physical manifestation of that. Now a table inside of your database is going to contain one or more columns, it cannot contain zero columns. So our database can’t contain a table that has no columns, it must contain at least one column, but it does not have to have rows or columns are a physical form of attributes, right. So remember, a relation is made up of attributes and tuples. Those attributes take the physical form of a column. Columns have a lot of different properties to them. Again, they are a set, not a list. So therefore, all columns inside of a table must be uniquely named right. So in this particular example, here, it would be impossible for us to have two columns called nickname, right. And so we have to have unique names for each column.

Name, all columns must also have a data type, and no ability. So whether or not that column can have data or whether or not that data is actually required. So a lot of records have inconsistent data. And some data is optional, right? When you fill out a form on the internet, or even in general, right, not all fields are typically required, depending on the situation that you have, the non required fields are allowed to be no, right. This diagram that I have here on my slide is an entity relationship diagram. This will look very similar to since you’ve encountered UML. Before, this is just the database version of that typical style of diagram. So on the left hand side, we have the indication of whether or not this column is a key, we have the individual columns listed here, the columns data type on the right, and then of course, the table name here at the top. Now also notice that we typically will also include the schema that that table belongs to remember schemas act kind of like a namespace inside of a database. So all tables for a particular database are contained inside of a schema.

And so and we’ll talk in a variety of different lectures about the individual data types here. And I’ll talk a little bit more about them. And this particular lecture as well. But we won’t get into a lot of the details just yet. But let’s take a look at some examples here on how to actually create our first table inside of our database.

So by this point, you should have watched the video and read the material on how to get Microsoft SQL Server installed, as well as as your data studio or how to reuse the remote connection in order to access an already fixed or correct installation. We’ll be using this throughout the class in order to actually write to do our exercises, or the notes that we that I showcase in lecture are in these videos. And we’ll also be utilizing this for our projects and for our homeworks but for now let’s create our first database. If this is your first time watching this video, and first time going through these examples, you will have to create your first database and the syntax or the the way we actually do that is using the Create statement, and then the name of the database that we want to create and in this case, I’m going to create a database called CC 520. And we need to also make sure that we are connected to our local installation of our database. And so in this case, I’m just going to connect to my local installation of SQL Express that is running. And then if you already have other databases in your local installation that are already created, you’ll see those pop up here.

But if you’re on your master connection here, you can actually change down here towards the bottom, you can actually change whichever database that you’re actually connecting to, just as a refresher, and if you ever want to change that connection, you can just change it up here to where it says attached to. But for now, let’s go ahead and say create, sorry, create database CC 520. So create databases, the statement, and then the name of our actual database there are remember each, just like what we do with programming each SQL statement reach query that we execute as part of our database or for our database, we need to end that with a semicolon. Now, as you’ll soon find out, not every line of SQL code will actually have a semi colon following after it. But let’s go ahead and create our database here.

So commands completed successfully. Now, keep in mind as well, if you’ve already created this database on your system, you will get an error. It says, Well, this database cc 520 already exists. So you don’t have to run this more than once. So we also need to create our first schema as part of our database as well. So just like what we did with our database above, create schema, and then we’re going to create a demo the demo schema, and then we’re going to set authorization to be DBO. We kind of hand wave over the authorization part here. This deals with permissions set for the schema, and we’ll talk about that later in the course. But for now, we just want to create this schema called demo. And note that the square brackets are TC equals or SQL servers, syntax for denoting names for database objects. Now, you can also use quotation marks here as well, quotation marks will work just the same. But the Microsoft SQL Server way of doing it would be the square brackets. Now, also note that before you actually run this create schema, we also need to make sure that we are using the correct database. Otherwise, your schema will be created in the database that whatever that your whatever your connection is actually physically attached to. And so since my connection is not connected, or directly connected to my individual database that I just created, I need to actually set use the go statement or use statements and then say, go.

So what go is going to do is it’s going to first execute this UCC 520 and then execute the Create schema. And so if I run this, both of those commands were done successfully. So this command was executed first, and then this command was executed second. Now, in theory as well, what we can do to fix this, at least as far as as your Data Studio goes, is that we can actually change our connection here. And instead of connecting to master, so we click on master first to load up all of this. And then down here for our database, we can say cc 520. Instead, press Connect. And now our connection is set to cc 520. And we don’t actually have to use the using statement or use statement to make sure we’re on the correct database.

But now that we have our basic schema and our basic database, let’s go ahead and add our first table. now. I’ll in the notes, you’ll find that I have this drop table if exists command. This is only there to if you’ve already ran through these notes. If the table exists already, you’ll get an error as a result. So as far as this example goes, you’ll want to drop the table first. So delete the table from the database. And then you can run that CREATE TABLE statement again. So let’s go ahead and do that. So we’ll do this and a couple ghosts. So drop it, drop it if it exists. And then we’re going to say create Table Table demo dot school.

So when we create table, when we create a table, the schema name needs to be included as part of the naming conventions. So that way your table, if you have more than one schema, you know which table that schema actually belongs to. So that is good practice there. So schema dot table, just like what we had with our diagram, right there. So we can see that our table name would be demo dot school. And we have each of our individual, we have each of our individual columns here. So this is the column name. Now note, I do have to put the square brackets around name here, because if I take those out, you notice that the color changes to blue. That means that it’s being recognized as a reserved word in T SQL. And so we need to either put double quotes around it or square brackets around it. So it knows that I’m intending to use it as an actual name of the column, and not part of what the reserved word represents in T SQL. And then right next to that, we’ll have our data type for the column. And then we’ll also have any constraints associated with that as well. But we’ll add those later, and in just a few minutes, but each of the columns are separated out by column as are, each of the columns are separated out by a comma, as you see here. And then again, all of those columns are wrapped inside of parentheses. And then of course, we end our SQL statement with a semi colon.

But if we run this, we see that our command is running successfully. And now I can actually browse over here into my databases. And if I refresh, I go to my cc 520, expand my tables. And now here is my demo dot school. And I can actually see all of the individual individual columns here, along with their data types associated with them. Now notice, as well, by default, if they are not, if it’s not specified, the columns are set to be not meaning that I don’t actually have to store any data for those particular columns, right? They’re nullable. And we’ll talk about the not null constraint here in just a minute. But let’s go ahead and also add some basic data associated with this. So if we look, let me go ahead and actually just touch it to the cell here. So we have inserts, which is the SQL statement to actually add more data to our data or a particular table. And we’ll talk about the insert statement later in the semester, as we get closer to that to data modification. But for now, just know that the insert statement will take a list of columns for a table, and then all of the values associated with it. So each of these row, each of these sets of parentheses are tuples, right. So this is one tuple, comma, another tuple. Remember, each tuple represents a row in our relation or table.

And each of these is in order, corresponding to each of the columns that I have up above. Now, if we run this, eight rows affected, so that means all of my eight rows of data got inserted into my table. So let us do a little bit more before we actually start moving on. So I’m going to hide this real quick. And then I’m going to…let’s go ahead and make sure our data got inserted. So I’m going to select star from demo school. Notice that my IntelliSense is thinking that demo school demo dot school does not exist. And you can safely ignore that I’m connected to the right database and I am that table is actually created, but some caching actually occurs here. And so as your Data Studio doesn’t always properly refresh right away. And so if you just created a table, and during this session, as your Data Studio may not recognize that that table actually exists yet.

But once you run that SELECT statement, so select star, and remember, select is the projection. So this picks which columns in our database that we actually want, star is a wildcard, meaning I want all of the columns. And then from is going to pick which tables I want to pull data from. And so in this case, demo dot school. But again, a lot of these SQL statements here, we’ll be covering in much more greater detail in future talks. But for now, we can see that all of our data exists in our particular table, as we inserted it just before.

So let’s take a look at a another example. So let’s move my screen just a little bit higher here. So here’s another big example. And if you want to take a pause here to type this out, now would be a really good time to pause the video to actually get this SQL code into your Azure Data Studio. Or you can copy this from the sequel notes as well. But I encourage you to start typing these out to get that muscle memory in. So just like when he first started to learn how to code, typing out SQL statements is going to be the best practice to actually get familiar with the syntax.

But let’s take a look at what happens here. So I had added, I added a new column here called a website, just to kind of explore here, what would happen down here in my table, so this was not included in my original table that I have up here. But it is included in this CREATE TABLE statement. But my insert has no website column. So if I run this, and then if I pull my statement from up there, and run by select star, you’ll notice that my website column all contains null values. That’s because I did not say that this website column could be or could not be no. So by default, it is normal, right? Meaning that I don’t actually have to insert data for that particular column, when I run an insert statement, I could just pick and choose which columns that I wanted.

So, but a lot of times, the vast majority of times anyways, most columns in the database would be required, so meaning they could not be not. And that’s really important, because if we have a lot of data attributes that are normal in our table, we’re actually really not being very efficient with our database design. And likewise, not being very efficient with our data storage. So it’s really important that we be very careful and allowing nullable columns. But let’s take a look at what the do what the our basic constraints could start to.

But let’s take a look at some constraints that we can use to prevent no values being inserted in our database. So constraints are essentially declarative role. So just like what we have rules and logic, we can have rules in our database as well. So our database management system, or DBMS, will check these constraints. Every time new data is added. Data is modified. And in some cases, when data is deleted, and any operation that violates a constraint will fail and return an error. So typically, this would be an exception that gets thrown. So SQL Server, our database management system will manage these constraints for us. And so when we work with our data, adding new data, deleting or modifying data, those constraints can be checked and to verify that our data or our data maintains its integrity. And that is one of the biggest reasons why we actually are using a database over something like Microsoft Excel.

But let’s take a look at our first constraint here. So as we talked about just a little bit ago, we want to make sure that We don’t have as much nor data as part of our database, right? knowable data is okay, it’s perfectly acceptable. But if we have a lot of it, our database design and storage is not that efficient. So the knowability constraint allows us to indicate which values are mandatory and which ones are not. So a value that a value is really anything that fits the domain of that column that is not null.

So in terms of a invar, char, a variable length string, that is 64 bits in length, or 64 characters in length, anything that is text or string will actually fit into that domain unless it’s no right meaning non existent. Similar thing for integers are n chars, which is a fixed length string. Again, we’ll talk about these different domain types or different data types in another class period.

Now, take a look at our ER diagram here. Now as well, we have changed a little bit of the of the structure here just a little bit, but pay really close attention to the that the name year stablish, nicknamed colors, city and state code are now a talyc. thing. So in an ER diagram, if your column name is italicized, it means that it is knowable, meaning that it is not required. If the text is not italicized, that means that column is required. So now, in this diagram, we’re making the website field required.

So let’s take a look at what that would look like in SQL. So if we recreate our table here, let’s go ahead and copy this in here. And so I know this is a lot of SQL code. But these videos would be extremely long if I sat here and typed out all of the SQL by hand, every single for every single example. So please bear with me, if you want to take some time to read through these SQL statements very carefully, please do pause the video for a moment. So you can read what’s going on here.

So we’re going to remove our old table. And now we’re going to create the demo duck school table again, and search the same values. And then we’re going to select everything to see what is left in it. Now keep in mind here, the change that I made is in the creation statement, I added a constraint now. And so we have our column name here, followed by our domain or column type. So this is a string that is variable length up to 128 characters. Now I am specifying that this column is not No. So by default, these columns if you do not include the knowability constraint, it by default has no in its place. And so it allows that column to be optional. But if we say not know, that column all of a sudden becomes required. So let’s go ahead and try to run this code again.

Ah, so cannot insert the value null into column website. Table cc 520 dot demo dot school column does not allow nulls insert fails, that statement has been terminated. And so when we do our select, nothing actually happens, right? Nothing is actually there inside of our table, because no data got inserted to begin with. So we have essentially restricted this. So any data that we actually try to add in our table, our database management system, SQL Server prevents us from inserting a record into the school table without the school’s website associated with it.

Now, let’s go ahead then and also make the rest of our columns not null. So here we go. And notice as well, I went ahead and took out the I went ahead and took out the website column here. But this should now work, right. But note if I ever happened to take out, let’s say one rep one value here and try to run this again. Notice that the insert fails because the number of columns for each row have to match the values up here, unlike what we had here, because we have unknowable, so we have name, year. Nickname, colors, the state, but now, right website is not included as as a column as part of my insert because I’m not actually inserting that value. So just keep in mind that that is also a restriction that we have for insertion.

But if we actually started to try to say, no here, huh, that statement has been terminated because of the error cannot insert null into the column state code, because that column does not allow nulls. And so we want to take this back. If I run this now. Everything is all good again.

All right. So these sorts of constraints may seem very simplistic at first, but especially when you have a database that is connected to a some sort of form, or anything like that is really important, as far as the database goes is to help ensure that your data is valid, ensure that your data is the data integrity actually holds up really well. A lot of times applications don’t have consistency with data. And so the database can help us enforce that consistency. Right. And again, that is one of the big reasons one of the big benefits that we are trying to leverage moving away from an Excel like file like an Excel sheet or txt file, something like that to something that is managed like a database