SQL simple -> Basic Data Definition syntax for MS-Access (JetSQL)

Create and Alter table statements

Examples:
CREATE TABLE Departments
(DeptNo Autoincrement,
DeptName Char(45),
BuildingNum Integer,
CONSTRAINT pk_DeptNo PRIMARY KEY (DeptNo));

CREATE TABLE Employees
(EmpNo Autoincrement,
DeptNo INTEGER NOT NULL,
FirstName CHAR(20),
LastName CHAR(30),
DateOfBirth DATETIME NOT NULL,
CONSTRAINT [pk_Key] PRIMARY KEY (EmpNo),
CONSTRAINT [emp2_constr_uq] Unique (DeptNo,FirstName)
);

ALTER TABLE Employees
ADD CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo)
REFERENCES Departments (DeptNo);

ALTER TABLE Employees DROP CONSTRAINT fk_DeptNo;

/* if you do not specify CHAR(?) size it will recive default size: 256 bytes. Autoincrement datatype is LONG by default */

ALTER TABLE Employees ADD COLUMN Notes TEXT(25);

ALTER TABLE Employees DROP COLUMN Salary;

ALTER TABLE Employees ALTER COLUMN Salary CHAR(20);

Create Table and add Primary Key constraint:
Examples:
CREATE TABLE Orders
(CustId INTEGER,
CLstNm CHAR(50));

ALTER TABLE Orders
ADD CONSTRAINT pk_OrdersId
PRIMARY KEY(OrderID);

Constraints general syntax:

Single-field constraint:

CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]}

Multiple-field constraint:

CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
NOT NULL (notnull1[, notnull2 [, ...]]) |
FOREIGN KEY [NO INDEX] (ref1[, ref2 [, ...]])
REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]}

Examples:
CREATE TABLE Orders
(OrderId INTEGER PRIMARY KEY,
CustId INTEGER,
OrderNotes NCHAR VARYING (255),
CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId)
REFERENCES Customers);

CREATE TABLE Orders
(OrderId INTEGER PRIMARY KEY,
CustId INTEGER, OrderNotes NCHAR VARYING (255),
CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId)
REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL);

/* ON UPDATE CASCADE, ON DELETE CASCADE seems not to be supported in MS Access 97 */

Create Index general syntax:

CREATE [ UNIQUE ] INDEX index
ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Examples:
CREATE UNIQUE INDEX CustID
ON Customers (CustomerID)
WITH DISALLOW NULL;

CREATE INDEX NewIndex ON Employees
(HomePhone, Extension);

[Access 2000 (Jet 4) additions to SQL syntax

AUTOINCREMENT datatype column now have starting value and can be increased by specific number. AUTOINCREMENT synonym - COUNTER

ALTER TABLE Employees ALTER COLUMN EmpNo
AUTOINCREMENT (1,1);

/* you can retrieve last value of AutoIncrement column in SQLserver style syntax */
SELECT @@IDENTITY;

/* set default value for the column */
ALTER TABLE Employees ALTER [COLUMN] DeptNo SET DEFAULT 5;
ALTER TABLE Employees ALTER [COLUMN] DeptNo DROP DEFAULT;

/* create vew like in SQLserver */
CREATE VIEW MyCrazy_Example AS
SELECT emp.FirstName, emp.LastName, dp.DeptName
FROM Employees AS emp INNER JOIN Departments AS dp
ON emp.DeptNo = dp.DeptNo

/* create procedure */
CREATE PROCEDURE myCrazy_Proc
(intNum Integer)
AS
SELECT * FROM Employees
WHERE DeptNo = intNum

/* execute procedure */
EXECUTE PROCEDURE myCrazy_Proc 3



sqlexamples.info