All tables in the same schema must have unique names. Tables cannot have the same name as a view. When we create a new table, we can omit the schema name. In this case, the table will be created in the current schema.
This is the simplest CREATE TABLE statement. We need to specify table name, column name and column data type. CREATE TABLE Tab1 ( Col1CLOB );
If we try to create a table with the same name again, we will get an error. CREATE TABLE: name 'tab1' already in use
We can avoid that error with clause "IF NOT EXISTS". A new table will only be created if no other table with that name exists. If a table with that name already exists, nothing will happen, but we won't get an error. CREATE TABLE IF NOT EXISTS Tab1 ( Col1 CLOB );
If we want to create a table in a non-current schema, we must use the fully qualified table name. Of course, we have to have enough privileges for that. CREATE TABLE sys.Tab1 ( Col1 CLOB );
After the data type we can include some options that better describe our column. Those options are DEFAULT, NOT NULL, PRIMARY KEY, UNIQUE.
In the system table sys.keys, we can now find our constraints for the primary key and for the unique constraint. Both of these constraints belong to table Tab2, which has ID 8271. SELECT * FROM sys.keys WHERE Table_id = 8271;
There is also a system table sys.columns where we can find columns named "col1". The column in the last row belongs to table Tab2, because table_id = 8271. SELECT * FROM sys.columns WHERE name = 'col1';
If we read from the table sys.tables, we can search for our tables by using their ID-s. SELECT * FROM sys.tables WHERE id = 8252 or id = 8271;
It is possible to insert nothing into our table. Such a statement will work. When we try to read from our table, we will see that the DEFAULT value has been written into it.
If we try to write a NULL value to our table, such an act will fail, because our column is defined as a NOT NULL column. INSERT INTO Tab2 VALUES (null);
If we try to write the default value again to our table, it will fail. We cannot have two rows with the same value in column Col1, due to primary key constraints.
In the real world, we would never mix DEFAULT and PRIMARY KEY constraints. PRIMARY KEY means that each row should be unique. That's the opposite of what DEFAULT is trying to do. PRIMARY KEY also means that our column does not accept nulls, so there is no need for a formal NOT NULL constraint. UNIQUE is also redundant as PRIMARY KEY will not allow duplicates anyway. In the real world, we would never use all the constraints on the same column.
Creating a Table Based on Some Other Table
Using LIKE operator
Previously, we created a table Tab2 with a column Col1 that has many constraints. Now we want to create a new table that will be a copy of Tab2, but will have a few more columns. We can do it in one step. This statement below will create all the columns found in the Tab2 table and place them in the Tab3 table. Tab3 will also have another column of type INTEGER.
CREATE TABLE Tab3 ( LIKE Tab2, Col2 INTEGER ); We can see on the image, Tab3 inherited "Col1" from the table Tab2. We also added one more column "Col2" in table Tab3.
Constraints on columns in Tab2 will not be inherited. If we read from system table sys.columns, we will notice that table with ID 8280 (Tab3), doesn't have the same constraints as the table 8271 (Tab2). All of the constraints are lost.
Using AS SELECT
By using AS SELECT statement we would create a table based on some SELECT query. We can type:
CREATE TABLE Tab5 ( Zcolumn, Today ) AS ( SELECT Col1, current_date FROM Tab2 );
The new table will not inherit the constraints from the old column. We can see that we don't have the same restrictions on the 'zcolumn' column as we did on the 'col1' column.
We don't have to provide aliases. We can use original column names. CREATE TABLE Tab6 AS ( SELECT Col1, current_date AS Today FROM Tab2 );
If add "WITH NO DATA" clause, then we would get the columns, but without data. CREATE TABLE Tab7 AS ( SELECT Col1, current_date AS Today FROM Tab2 ) WITH NO DATA;
Table Constraints
We can place PRIMARY KEY constraint on one column. It won't help us if our table has a composite primary key. If this is the case, we need to place constraints on the table itself. We can write the statement like this:
