SQL Database

DROP a CHECK Constraint

SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge; MySQL: ALTER TABLE Persons DROP CHECK CHK_PersonAge;

SQL Constraints

SQL constraints are used to specify rules for data in a table.

DEFAULT

Sets a default value for a column when no value is specified

FOREIGN KEY

Uniquely identifies a row/record in another table

INDEX

Use to create and retrieve data from the database very quickly

Create Table Using Another Table Syntax

CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....;

CREATE VIEW Examples

CREATE VIEW [Current Product List] AS SELECT ProductID, ProductName FROM Products WHERE Discontinued = No; Then, we can query the view as follows: SELECT * FROM [Current Product List]; CREATE VIEW [Products Above Average Price] AS SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products); We can query the view above as follows: SELECT * FROM [Products Above Average Price];

DROP TABLE Syntax

DROP TABLE table_name;

NOT NULL

Ensures that a column cannot have a NULL value

UNIQUE

Ensures that all values in a column are different

DROP INDEX Statement

MS Access: DROP INDEX index_name ON table_name; SQL Server: DROP INDEX table_name.index_name; DB2/Oracle: DROP INDEX index_name; MySQL: ALTER TABLE table_name DROP INDEX index_name;

DEFAULT on CREATE TABLE

My SQL / SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT 'Sandnes' ); CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() );

FOREIGN KEY on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

CHECK on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CHECK (Age>=18); MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

UNIQUE constraint on multiple columns 2

MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

PRIMARY KEY on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD PRIMARY KEY (ID); MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

DEFAULT on ALTER TABLE

MySQL: ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes'; SQL Server / MS Access: ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'Sandnes'; Oracle: ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes';

CHECK on CREATE TABLE

MySQL: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) ); SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) ); CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );

TRUNCATE TABLE Syntax

TRUNCATE TABLE table_name;

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

DEFAULT Constraint

The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified.

DROP DATABASE

The DROP DATABASE statement is used to drop an existing SQL database.

DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.

ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype; ALTER TABLE table_name MODIFY COLUMN column_name datatype; (Oracle)

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name;

UNIQUE Constraint

he UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

PRIMARY KEY

A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

CREATE INDEX Example

CREATE INDEX idx_lastname ON Persons (LastName); CREATE INDEX idx_pname ON Persons (LastName, FirstName);

CREATE INDEX Syntax

CREATE INDEX index_name ON table_name (column1, column2, ...);

CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL: CREATE OR REPLACE VIEW [Current Product List] AS SELECT ProductID, ProductName, Category FROM Products WHERE Discontinued = No;

UNIQUE constraint on multiple columns

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) );

CHECK

Ensures that all values in a column satisfies a specific condition

DROP DATABASE Syntax

DROP DATABASE databasename; DROP DATABASE testDB;

FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table points to a PRIMARY KEY in another table.

Create Table Using Another Table

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table gets the same column definitions. All columns or specific columns can be selected. If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

ALTER TABLE - ADD Column

ALTER TABLE table_name ADD column_name datatype;

SQL NOT NULL Constraint

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Syntax

CREATE DATABASE databasename;

AUTO INCREMENT Field Syntax for MySQL

CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement: ALTER TABLE Persons AUTO_INCREMENT=100; To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically): INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "ID" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

UNIQUE Constraint on CREATE TABLE

CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) ); MySQL:

NOT NULL

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int ); Tip: If the table has already been created, you can add a NOT NULL constraint to a column with the ALTER TABLE statement.

CREATE TABLE Example

CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

CREATE TABLE

CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );

CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...); Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE VIEW Syntax

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

Create Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement. CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... );

DROP VIEW Syntax

DROP VIEW view_name;

AUTO INCREMENT Field Syntax for Oracle

In Oracle the code is a little bit more tricky. You will have to create an auto-increment field with the sequence object (this object generates a number sequence). Use the following CREATE SEQUENCE syntax: CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10; The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access. To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence): INSERT INTO Persons (ID,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "ID" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

UNIQUE Constraint on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD UNIQUE (ID);

PRIMARY KEY constraint on multiple columns

MySQL / SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) );

DROP a FOREIGN KEY Constraint

MySQL: ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder; SQL Server / Oracle / MS Access: ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

DROP a DEFAULT Constraint

MySQL: ALTER TABLE Persons ALTER City DROP DEFAULT; SQL Server / Oracle / MS Access: ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;

DROP a UNIQUE Constraint

MySQL: ALTER TABLE Persons DROP INDEX UC_Person; SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT UC_Person;

DROP a PRIMARY KEY Constraint

MySQL: ALTER TABLE Persons DROP PRIMARY KEY; SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT PK_Person;

FOREIGN KEY on CREATE TABLE

MySQL: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); SQL Server / Oracle / MS Access: CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) ); MySQL / SQL Server / Oracle / MS Access: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

PRIMARY KEY on CREATE TABLE

MySQL: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );

ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.