Management of tables and relations
Create table
CREATE TABLE table (field1 type [(size)][NOT NULL] [index1], field2 type [(size)][NOT NULL] [index2] [, CONSTRAINT multipleindex])
Part Explanation table Name of the table you want to make field1, field2 Name of the fields you want to make in the table. At least 1. type Type of data the field should have. Size The field size in characters. Only for text-and binary fields. index1, index2 A component CONSTRAINT that combines a index with a singular field. multiple index A component CONSTRAINT that combines a index with multiple fields. With CREATE TABLE you can create a table with fields, including limitations for those fields. If you add NOT NULL, there must be something entered in that field.
Possible data-types. (NOTE: this can change depending on the database-software you are using.)
Name Size Explanation BINARY 1 byte/character Can contain any value, there is no conversion. BIT 1 bit Can only contain 0 or 1
Sometimes replaced by TINYINT(1)BYTE (TINYINT) 1 byte Can contain a number between 0 and 255 (unsigned or -128 to 128 signed) MONEY 8 bytes Can contain a number between -2*1063 and 2*1063. The number is shown with a currency-symbol. DATE 8 bytes Can contain a datum between the years 100 and 9999. DATETIME 8 bytes Can contain a datum and time-value between the years 100 and 9999. SINGLE REAL 4 bytes Can contain a single-precision floating-point number. DOUBLE FLOAT 8 bytes Can contain a double-precision floating-point number. SHORT SMALLINT 2 bytes Can contain a number between -32768 and 32767 LONG INTEGER 4 bytes Can contain a number between -2 147 486 348 and 2 147 483 647 CHAR 1 byte/character Can contain a string. The size can be max 255. VARCHAR 1 byte/character Can contain a string. The size can be max 255. The actual size will automatically change to the size of the string. (Works in MySQL) TEXT 65535 byte/character Can contain a large string. (Works in MySQL) ENUM a predefined list ENUM('true','false') or ENUM('small','medium','large'). (Works in MySQL) BLOB Can contain any value, there is no conversion Usefull for storing no text-vaules (compressed text, pictures, ...). (Works in MySQL) Example:
CREATE TABLE clients (clientnr integer not null, clname char(30) not null, claddres char(40), clcode char(4), clcity char(40))
This creates a table clients with fields: clientnr, clname, claddres, clcode, clcity. The fields clientnr and clname must contain a value.DROP table
This instruction removes a table from a database or deletes a existing index from a table.
DROP {TABLE tablename | INDEX indexname ON tablename}
Part Explanation tablename The name of the table you want to remove or the name of the table where you want to delete an index. indexname The name of the index you want to remove. Before you can remove a table or index, you have to close the table.
You can also make a index with ALTER TABLE.
With CREATE TABLE you can make a table and with CREATE INDEX or ALTER TABLE you can make a index.Example:
DROP TABLE clients
Removes the table clients from the database.ALTER
With this instruction you change the design of the table after it is made with the instruction CREATE TABLE.
ALTER TABLE tablename
{ADD {field type[(size)] [NOT NULL] [CONSTRAINT index] | CONSTRAINT multipleindex} | DROP {COLUMN field | CONSTRAINT indexname}}
Part Explanation tablename The name of the table you want to change. field Name of the field you want add, change or remove from the table. type Data-type of the field size Field size in characters (only for text- and binary fields) index The index for field. multipleindex The definition of a index with multiple fields you want to add to the table. indexname The name of the index. With the instruction ALTER TABLE you can change an existing table.
With ADD COLUMN you can add a new field. You give the field name, data type and (optional) the size.
ALTER TABLE persons ADD COLUMN remarks CHAR(25)
This instruction add a field remarks with a length of 25 characters, to the table persons.If you enter NOT NULL for a field, then there must be something entered in that field with every new record.
You can remove a field with DROP COLUMN. You only need to specify the name of the field.
To remove a index on multiple fields, use DROP CONSTRAINT.
You can only add 1 field at the time.
ALTER TABLE persons DROP COLUMN remarks
This instruction removes the field remarks from the table persons.You can also change a columns name or datatype. This differs from database system to database system.
MySQL ->ALTER TABLE table CHANGE old_name new_name INTEGER;
MSSQL ->ALTER TABLE table ALTER COLUMN column_name CHAR(7)
(To change a column in MSSQL, you have to make a new column, copy everything and delete the old)CONSTRAINT
Constraint is used to add limitations to your fields, but it can also be used to make relations with other tables.
You use CONSTRAINT when you make or removes these limitations. There are 2 types of CONSTRAINT-components: 1 to make limitations for 1 field and another to make limitations for more fields.
CONSTRAINT for 1 field:
CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES referencetable [(referentiefield1,referentiefield2)]}CONSTRAINT for more fields:
CONSTRAINT name
{PRIMARY KEY (primary1, primary2, ...)|
UNIQUE (unique1, unique2, ...)|
FOREIGN KEY (point1, point2, ...)|
REFERENCES referencetable [(referencefield1,referencefield2, ...)]}
Part Explanation name The name of the limitations you want to make. primary1, primary2, ... The name of the field or fields that are going to be Primary key. unique1, unique2, ... The name of the fields you want to make unique keys point1, point2, ... The names of the fields you want to link with fields in another table. referencetable The name of the reference table with the fields that are indicated by the referencefields. Referencefield1,referencefield2, ... The name of the fields in the reference table that who are indicated by point1, point2, ... . You don't have to write this if the fields indicated, is the primary key in the reference table. If you make limitations for 1 field, then you have to write the CONSTRAINT-part immediately after the data type.
The CONSTRAINT-instruction for more fields most be written outside the field-definition.With the word UNIQUE you can mark a fields as a unique key. You can make any field unique. In a unique fields there can be no 2 records with the same value.
With the words PRIMARY KEY, you can make 1 field (or more) the primary key. All records in the primary key must be unique and not null. There can be only 1 key for primary key in a table.
With the words FOREIGN KEY you can point a field as reference key. If the primary key of the reference table exists of more then 1 field, you have to add limitations for more fields, with the following: all the fields pointed to, the name of the reference table and all the names pointed to in the reference table in the same sequence how the fields are in the table (REFERENCES). If the field(s) pointed to, is the primary key of the reference table, you don't have to write it(them).
CREATE TABLE clients (clientnr integer NOT NULL CONSTRAINT pk_clientnr PRIMARY KEY, clname char(30) NOT NULL)
This instruction creates a table, with the fields clientnr(primary key) and clname. Both have to be entered.
CREATE TABLE user (nr integer NOT NULL CONSTRAINT pk_nr primary key, name char(30), uid char(8) CONSTRAINT u_uid unique)
Creates a table, users, with fields, nr(primary key), name and uid. The value in uid must be unique.Constraint is also used to define relations between tables.
With create table we define a table orders with the fields ordernr(primary key), clientnr, orderdate.
CREATE TABLE orders (ordernr integer CONSTRAINT pk_bestelnr primary key, clientnr integer CONSTRAINT f_clientnr REFERENCES clients (clientnr), orderdate datetime)
Between the clientnr of this table and the clientnr of the table clients, there is 1 on 1 relationship. The data type of both fields must be the same.
CREATE TABLE orderline (ordernr integer, biernr integer, number, integer, CONSTRAINT pk_bestbier PRIMARY KEY (ordernr,biernr))
This instruction creates a table orderline existing of the fields ordernr, biernr and number. There is a composed primary key of ordernr and biernr.
ALTER TABLE orderline ADD CONSTRAINT f_bestelnr FOREIGN KEY(ordernr) REFERENCES orders(ordernr)
Defines a 1-n relation between the field ordernr of the table orderline and the field ordernr orders.
ALTER TABLE orderline DROP CONSTRAINT f_bestelnr
Removes the condition(limitation) f_bestelnr.CREATE INDEX
With this instruction you make a new index for an existing table. If you often look for data through a field(s), it is recommended to place an index on that field. This increases response time.
CREATE [UNIQUE] INDEX indexname
ON tablename (field1 [ASC|DESC], field2 [ASC|DESC], ...)
[WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]
Part Explanation indexname The name of the index you want to make. tablename Name of the existing table where in the index is going to be placed. field1, field2 The name of the field(s) you want to add in the index. I you want a descending index you need to use DESC otherwise the index is ascending. Double values in the indexed fields can be prevented by using UNIQUE.
In the optional WITH-part, you can add rules to validate data.
Zero values in new records of the indexed fields are prevented by DISALLOW NULL.
With PRIMARY you can make the indexed field the primary key. The primary key is always unique, so you don't have to add UNIQUE.
Don't make a primary key if the table has already a primary key.You can also add a index with ALTER TABLE. You can remove a index with ALTER TABLE or DROP.
CREATE INDEX i_name ON clients (clname)
Create a index i_name on the field clname of the table clients.
DROP INDEX i_name ON clients
Removes the index i_name from the table clients.
