Developing By Me

Project Development and Design

CREATE TABLE

    The CREATE TABLE syntax is how new tables are added to a schema. If the table name exists an error will occur. In all cases, when a table is created using the CREATE TABLE it will be treated as a permanent table. Some RDBMS allow for temporary tables which can be created through the SELECT INTO or INSERT INTO statements and are then destroyed at the end of the script. However, any table created through CREATE TABLE will always remain unless it is dropped. The statement takes a table name and a field list with data types. The following is a typical table creation that could be used for any of the examples shown.

 

CREATE TABLE table1 (
field1 datatype,
field2 datatype,
field2 datatype
)

 

    The above is the standard syntax to create a table. However, there are more features that can be added to a table such as a primary key and default values which can be done when the table is originally created. The following is a standard SQL Server script to create a table with multiple options which will be explained afterword.

 

CREATE TABLE table1 (
id BIGINT PRIMARY KEY IDENTITY,
name VARCHAR(50) NOT NULL,
sales INT DEFAULT 0

)

 

    There is a bit of new information on this query. First is IDENTITY this means that the database will automatically assign a value to that row. Next is the syntax for NOT NULL. Every field in the database table defaults to allowing a NULL value. In some cases this is not desire able so for those fields the DBA uses NOT NULL to force the system to require a valid value. Finally is the keyword DEFAULT. This overrides the DEFAULT value of NULL and if one is not provided by the INSERT script this value will be provided. However, if the field allows for NULL, like the one above, and NULL is provided the DEFAULT value will not be used.

 

%d bloggers like this: