SQL CREATE TABLE statement \sql constraint (Constraints) \sql not NULL constraint \sql UNIQUE constraint

CREATE TABLE Statement

The CREATE TABLE statement is used for creating tables in the database.

SQL CREATE TABLE Syntax

CREATE table table name (column name 1 data type, column name 2 data type, column name 3 data type,....)

The data type (DATA_TYPE) specifies what data type the column can hold. The following table contains the most commonly used data types in sql:

Data Type	Description
Integer (size) int (size) smallint (size) tinyint (size)	Holds integers only. Specify the maximum number of digits within the parentheses.
Decimal (SIZE,D) Numeric (SIZE,D)	Accommodates numbers with decimals. "Size" Specifies the maximum number of digits. "D" Specifies the maximum number of digits to the right of the decimal point.
char (size)	Holds a fixed-length string (which can hold letters, numbers, and special characters). Specifies the length of the string in parentheses.
varchar (size)	Accommodates variable-length strings that can hold letters, numbers, and special characters. Specifies the maximum length of the string in parentheses.
Date (YYYYMMDD)	accommodate the date.

SQL CREATE TABLE Instance

This example shows how to create a table named "Person."

The table contains 5 columns with column names: "Id_p", "LastName", "FirstName", "Address", and "City":

CREATE TABLE Persons (id_p int,lastname varchar (255), FirstName varchar (255), Address varchar (255), City varchar (255))

The data type of the id_p column is int, which contains integers. The data type of the remaining 4 columns is varchar, with a maximum length of 255 characters.

An empty "Persons" table looks like this:

City

id_p	LastName	FirstName	Address

You can use the INSERT into statement to write data to an empty table.

SQL constraints

Constraints are used to restrict the type of data that is joined to a table.

You can specify constraints (through the CREATE TABLE statement) When you create the table, or you can (via the ALTER table statement) after the table is created.

We will focus on the following constraints:

• Not NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

SQL not NULL constraint

The NOT NULL constraint enforces that the column does not accept null values.

A NOT NULL constraint forces a field to always contain a value. This means that if you do not add a value to the field, you cannot insert a new record or update the record.

The following SQL statement enforces that the "id_p" column and the "LastName" column do not accept NULL values:

NOT NULLNOT NULL, FirstName varchar (255), Address varchar (255), City varchar (255))

SQL UNIQUE constraint SQL UNIQUE constraint

Unique constraints uniquely identify each record in a database table.

Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection.

PRIMARY KEY has a UNIQUE constraint that is automatically defined.

Note that each table can have multiple UNIQUE constraints, but there can be only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint in the "id_p" column when the "Persons" table is created:

Mysql:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), UNIQUE (Id_P) )

SQL Server/oracle/ms Access:

UNIQUE, LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

If you need to name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) )

SQL UNIQUE Constraint on ALTER TABLE

When a table has been created, to create a UNIQUE constraint in the id_p column, use the following SQL:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD UNIQUE (Id_P)

To name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

Revoke a UNIQUE constraint

To revoke a UNIQUE constraint, use the following SQL:

Mysql:

ALTER TABLE PersonsDROP INDEX uc_PersonID

SQL Server/oracle/ms Access:

ALTER TABLE PersonsDROP CONSTRAINT uc_PersonID

SQL CREATE TABLE statement \sql constraint (Constraints) \sql not NULL constraint \sql UNIQUE constraint