Create Table in SQL - sql - sql tutorial - learn sql
- The CREATE TABLE Statement is used to create tables to store data.
- Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table.
- The integrity constraints can be defined at column level or table level.
- The implementation and the syntax of the CREATE Statements differs for different RDBMS.
- A commonly used CREATE command is the CREATE TABLE command.
- The typical usage is: CREATE TABLE [table name] ( [column definitions] ) [table parameters]
- table_name - is the name of the table.
- column_name1, column_name2.... - is the name of the columns
- datatype - is the datatype for the column like char, date, number etc.
- If you want to create the employee table, the statement would be like,
Create Table Using Another Table:
- A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement.
- The new table gets the same column definitions. All columns or specific columns can be selected.
- If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Six types of constraints can be placed when creating a table:
- NOT NULL Constraint: Ensures that a column cannot have NULL value.
- DEFAULT Constraint: Provides a default value for a column when none is specified.
- UNIQUE Constraint: Ensures that all values in a column are different.
- CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
- Primary Key Constraint: Used to uniquely identify a row in the table.
- Foreign Key Constraint: Used to ensure referential integrity of the data.
- To create a customer table with the fields specified in the second paragraph above, we would type in
- No constraints were specified in the above SQL statement. What if we want to add a constraint that says the default country is 'United States'?
- In other words, if no data is entered for the "Country" column, it would be set to 'United States.' In that case, we would type the following SQL:
- Sometimes the table structure we need is the same as another table in the database.
- In this case, we can also use CREATE TABLE to make a copy of the table structure so we do not need to type all the column names, data types, and constraints in detail.
- The syntax for doing this is:
- To copy both the structure and data of Table1 into Table2, we would issue the following SQL statement:
- To copy the structure of Table1 into Table2 without any data, we would issue the following SQL statement:
- The WHERE 0 = 1 clause is always false. Therefore, no row of data will be copied from Table1 to Table2. Only the table structure is copied over.