In this topic, we described about the Table with detailed example.
Table is nothing but the set of rows and columns that are logically related. In another words, table is nothing logical structure of storing data. Table name is an identifier up to 128 characters.
Table is a collection of rows which are having the same columns or attributes. The SQL identifier used to qualify the table name is called as SCHEMA. CREATE TABLE s used to create the table.
Table definition must include the table name and the attributes of its columns. SYSADM and SYSCTRL authority is required for to create a table. If the ID didn’t have the above specified authority, then it will return -551 error when the CREATE TABLE triggered.
CREATE TABLE Table name (Column1 data-type [NOT][NULL] [WITH DEFUALT default-value], Column2 data-type [NOT][NULL] [WITH DEFUALT default-value], Column3 data-type [NOT][NULL] [WITH DEFUALT default-value], . . Column-n data-type [NOT][NULL] [WITH DEFUALT default-value] Primary key/foreign key column1,column2….column-n, [LIKE reference table name]
Column name is used to identify the data in the column.Column name mostly describes about the characteristics of attribute/column. Column name maximum length is 30 characters.
Data type used to specify the type of the data going to store in the particular attribute or column. Every column in the table must have a data type. The data type also can specify the range of column/attribute.
The data type needs to specify during the creation of table.All the values inserting into the column or attribute should follow the data type. The data types of a column can also change.
The new data type can applied to the data associated in the column during the reorganization.
NULL data type is used to represents that the value is absent in the column.All data types accept the NULL value. If the column declaration not having NULL indicator or only NULL, then the column will allows inserting NULL value. NULL topic explained in detail here .
WITH DEFAULT is used to insert a default value when no value given during the INSERT. If column declaration not having WITH DEFAULT and no value to insert, DB2 will not provide any default values during the INSERT.
If column declaration having WITH DEFAULT, DB2 will provide default values based on the data type during the INSERT. WITH DEFAULT topic explained in detail here .
- Uniquely identified column which is used to identify the row uniquely is known as primary key.
- Primary keys definition is optional in CREATE TABLE or ALTER TABLE.
- Primary key can be defined during the CREATE TABLE or ALTER TABLE.
- Primary key can be only one column or more than one column. Primary Key concept explained in detail here .
Foreign key is the key which is reference from another table. In other words, Foreign Key is a Primary key on other table(Parent table) and that can be referred in current table(child table) with other column name or same column name.
Foreign key can be defined in either CREATE TABLE or ALTER TABLE.Foreign key definition is optional in CREATE TABLE or ALTER TABLE.Foreign key can have a NULL values in the table(Child table) where it is declared as Foreign key.
Foreign key will create the referential constraint between the Parent table and Child table. Foreign Key concept explained in detail here .
LIKE used to create a new table as the same as the existing table. In other words, creating a table that has exactly the same name and description as the columns of the identified table or view.
The table name specified after LIKE should be identified in the existing system.
LIKE parameter concept explained in detail here .
Below example is to create simple table with one primary key
CREATE TABLE USER_DETAILS (USER_ID CHAR(6) NOT NULL, USER_NAME CHAR(20) NOT NULL, USER_MAILID CHAR(20) NOT NULL, PRIMARY KEY (USER_ID) ) IN MTH1DB.MFTHTS;