CREATE TABLE

CREATE TABLE

Creates a new table.

Example 1 of Create Table

CREATE TABLE Table_One(name char(30));

Table named 'Table_One' is created with just one column 'name'.

Example 2 of Create Table

CREATE TABLE Sells (
Food_Court CHAR(20),
Food CHAR(20),
price REAL
);

Table named 'Sells' is created with three columns 'Food_Court', 'Food' and 'price'.

Example 3 of Create Table

CREATE TABLE Sells (
Food_Court CHAR(20),
Food VARCHAR(20) DEFAULT 'Burger',
price REAL NOT NULL,
PRIMARY KEY (Food_Court, Food)
);

Table named 'Sells' is created with three columns 'Food_Court', 'Food' and 'price'.
For column Food, the default value is 'Burger' and column price has a NOT NULL constraint.

PRIMARY KEY is a combination of columns Food_Court and Food.

Example 4 of Create Table

CREATE TABLE Sells (
Food_Court CHAR(20),
Food VARCHAR(20),
price REAL,
UNIQUE(Food_Court,Food)
);

Table named 'Sells' is created with three columns 'Food_Court', 'Food' and 'price'.
UNIQUE key is a combination of Food_Court and Food column.

Example 5 of Create Table

CREATE TABLE Sells (
Food_Court CHAR(20) UNIQUE,
Food VARCHAR(20) UNIQUE,
price REAL
);

Table named 'Sells' is created with three columns 'Food_Court', 'Food' and 'price'.
Food_Court column is a UNIQUE key and Food column is a unique key.

Both Example 4 and Example 5 are different in terms of UNIQUE key.

Example 6 of Create Table

CREATE TABLE Foods (
name CHAR(20) PRIMARY KEY,
manf CHAR(20));

CREATE TABLE Sells (
Food_Court CHAR(20),
Food CHAR(20) REFERENCES Foods(name),
price REAL);

In Sells, Food is a Foreign key that references the Primary key 'name' of Foods table.

Example 7 of Create Table

CREATE TABLE Sells (
Food_Court CHAR(20),
Food CHAR(20),
price REAL,
FOREIGN KEY Food REFERENCES Foods(name));

Alternative way to declare a foreign key.

Example 8 of Create Table
(for this example take into consideration Foods table of example 6)

CREATE TABLE Sells (
Food_Court CHAR(20),
Food CHAR(20)  CHECK (Food IN (SELECT name
                               FROM Foods)),
price REAL   CHECK (price <= 5.00)
);

CHECK is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. Check on Food is like a foreign-key constraint, except: Check occurs only when a 'Sells' tuple is inserted or updated and Not when a 'Foods' tuple is deleted.

Example 9 of Create Table

CREATE TABLE Sells (
Food_Court  CHAR(20),
Food  CHAR(20),
price  REAL,
FOREIGN KEY Food REFERENCES Foods(name)
ON DELETE  SET NULL
ON UPDATE  CASCADE);

ON DELETE {CASCADE | NO ACTION}
Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.