SQL Database Constraints

SQL Server

DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: a unique number

SQL Date Data Types Mysql

DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: YYYY-MM-DD HH:MI:SS YEAR - format YYYY or YY

SQL DEFAULT Constraint

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' );

SQL 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 UNIQUE Constraint

The 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.

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

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) );

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: priimary keys

CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );

SQL UNIQUE Constraint on CREATE TABLE

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

SQL NOT NULL on CREATE TABLE

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

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) );

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) );

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.

CREATE INDEX Example

CREATE INDEX idx_lastname ON Persons (LastName);

SQL 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 Working with Dates

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

SQL 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.

SQL 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.

SQL 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.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

SQL Dates

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated.