Create new tables - SQL教程

- [Instructor] Now that we have learned how to fetch and filter data from existing tables, let's learn how to create new tables. For this, we use the create statement. It is a DDL statement, that is, DDL stands for Data Definition Language, and all DDL statements are auto commit, which means that once they are executed the changes will be permanent and cannot be reverted back. This is the syntax for the create statement. We can check if the table is created by giving describe table name or desc table name. Tables which do not belong to this user are not in this user's schema. If we are creating a table in a different schema, we will need to specify the schema name in the create statement. We can also create a table using a sub query. We use this method when we want to create a table by using another table's data. But this feature is available in a few databases like Oracle. If column specifications are given, then number of column specifications and number of columns in the sub query must be equal. When using sub query method, column definition can contain only column names and default values, but no constraints. Now, let's understand what are constraints. Constraints are like rules to follow at table level or column level. We can define these constraints at the time of creation of the table, or after the table has been created. Constraints that are defined must be satisfied to completely execute the statements. Otherwise, the statements will not be executed and it throws some errors. We have to name the constraints for easy identification. Now, let's take a look at some of the constraints. Primary key is a unique and not null value for every row. This helps identify every row in the table. Not null constrained, as the name implies, specifies that a column cannot contain a null value. Let's look at an example. Let's go to chapter two, video one. So here, we are giving create table countries and then we are giving all the column names. Here, if we observe carefully, the not null constraint is given with the column specification, as it is a column level constraint. If we see here, we are giving it along with the column. So, we gave it the column name as country code, the data type, followed by the constraint, the constraint name, and what kind of constraint it is. So, we are defining it near the column specification. So, it is a column level constraint. The primary constraint is a table level constraint, as it is specified after all the column specifications. If we notice carefully here in the code, this constraint, which is the primary key constraint, is given all the way after the column specifications are done, and not like the not null constraint, as we give with the column specification. So, the primary key constraint is a table level constraint. The third constraint is foreign key or referential integrity constraint. This assigns one or more columns as foreign key and establishes a connection with the primary key of the same or different table. Foreign key values must match with a value in the parent table or must be null. Let's look at an example for the foreign key. So here, for this table, the primary key is the state ID. The foreign key is defined on country ID. If you take a look over here, the primary key for states table is state ID, and the foreign key here is imposed on the country ID. So, this establishes a connection between the country ID from this table and the country ID from the countries table. So if we see, it is referencing the country ID from countries table. So, this means that we can insert a country ID in the states table only if that country ID is in the countries table. Otherwise, it throws an error. Similarly, we cannot delete a country ID from the country table while that country ID is mapped with some states in the states table. It again throws an error, since there are some dependencies on that country ID in the states table. So, we call countries table as the parent table and states table as the child table. So, this is how primary key and foreign key work hand in hand.