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