CPSC340A - Day 12 Notes

Review Create Table

Create table with name, column names, column types
```
CREATE TABLE TableName (id INT, value TEXT);
```
Note that there are multiple numeric and string types that may be more efficient (in memory and / or computation)
Integers have the following types TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT
Floating point numbers have the type FLOAT, DOUBLE
Fixed point numbers have thet type DECIMAL(M, N), where M is the number of digits to the left of the decimal point and N is the number of digits to the right
The DECIMAL type will store decimal numbers without approximating like a floating point number may
Strings can be CHAR(M), VARCHAR(M)
The CHAR type always uses M bytes
M can not be larger than 255
Padding is automatically added and removed if the text is less than M characters
The VARCHAR only uses as much storage as is needed, up to M bytes
There is, however, overhead (in space and time) in using a VARCHAR
To store more than 255 bytes of text there are the text types, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
These can use up to 2⁸, 2¹⁶, 2²⁴, 2³² bytes to store text
To store things other than numbers and text, like images or sounds, use the blob types, TINYBLOB, BLOB, MEDIUMBLOB, LARGEBLOB
The blob types store binary data as strings
There are also data and time types that conserve memory by storing less information
See the SQL documentation for all of the types
To see how a table was created
```
SHOW CREATE TABLE TableName;
```

Add / Drop Column

It is possible to add or remove a column after a table has been created

ALTER TABLE useless ADD new_value INT;
ALTER TABLE useless DROP COLUMN new_value;

Default Value

When adding a column, all existing rows get the default value

The default value can be changed when the table is created

CREATE TABLE TableName (
    id INT DEFAULT 0,
    value TEXT
);
INSERT INTO TableName (value) VALUES ('text');

Default value can be null, but this is the default

CREATE TABLE TableName (
    id INT DEFAULT 0,
    value TEXT DEFAULT NULL
);

Can also change the default in an existing table

ALTER TABLE TableName ALTER id DROP DEFAULT;
ALTER TABLE TableName ALTER id SET DEFAULT -1;

Not Null

Even if the default is not null, a cell can be set to null

To prevent null values when the table is created

CREATE TABLE TableName (
    id INT NOT NULL
);
INSERT INTO TableName (0);
INSERT INTO TableName (NULL);

Can have default and not null

CREATE TABLE TableName (
    id INT NOT NULL DEFAULT 0
);
INSERT INTO TableName (0);
INSERT INTO TableName (NULL);
INSERT INTO TableName ();

Can add or remove not null

ALTER TABLE TableName MODIFY id INT NOT NULL;

Need to respecify the entire thing (so if had default, need that as well).

Unique

Primary keys in a table can not be duplicated

Can enfore this when creating a table by using UNIQUE

CREATE TABLE TableName (
    id INT,
    value TEXT,
    UNIQUE (id)
);
INSERT INTO TableName VALUES (0);
INSERT INTO TableName VALUES (0);

Note the syntax for this is different, the UNIQUE goes at the end of the colum list
Now it is an error to add duplicate values

Unless is the value is NULL, can always have multiple nulls

INSERT INTO TableName VALUES (NULL, 'text');
INSERT INTO TableName VALUES (NULL, 'text');

Can prevent this by preventing nulls

CREATE TABLE TableName (
    id INT NOT NULL,
    UNIQUE (id)
)
INSERT INTO TableName VALUES (NULL);

Can add and remove uniqueness

ALTER TABLE TableName DROP INDEX id;
ALTER TABLE TableName ADD CONSTRAINT id UNIQUE (id);

Primary Key

A primary key is unique and can’t be null

PRIMARY KEY makes this easier

CREATE TABLE TableName (
    id INT,
    PRIMARY KEY (id)
);
INSERT INTO TableName VALUES (1);
INSERT INTO TableName VALUES (2);
INSERT INTO TableName VALUES (2);
INSERT INTO TableName VALUES ();

Must be a non-null column to be a primary key, if not, mysql will make it one
There can also only be 1 primary key per table

There can be multiple non-null, unique keys.

CREATE TABLE TableName (
    id INT NOT NULL,
    value INT NOT NULL,
    UNIQUE (id),
    UNIQUE (value)
);
DROP TABLE TableName;
CREATE TABLE TableName (
    id INT,
    value INT,
    PRIMARY KEY (id),
    PRIMARY KEY (value)
);

If you have a primary key, they all need to be unique and non-null on creation
We solved this previously using this max + 1, but this can get annoying

So sql has auto increment

CREATE TABLE TableName (
    id INT AUTO_INCREMENT,
    PRIMARY KEY (id)
);
INSERT INTO TableName VALUES ();
INSERT INTO TableName VALUES ();
INSERT INTO TableName VALUES ();

Note that the column must be a primary key, and must be numeric type

Foriegn Key

Can also specify that a column is a foriegn key

CREATE TABLE TableName (
    id INT,
    FOREIGN KEY (id)
        REFERENCES OtherTableName(id)
);

This prevents a referened row from being deleted without deleting the row with the foreign key first

CREATE TABLE Parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);
CREATE TABLE Child (
    id INT,
    parent_id INT,
    FOREIGN KEY (parent_id)
        REFERENCES Parent(id)
);
INSERT INTO child VALUES (0, 1);
INSERT INTO Parent VALUES ();
INSERT INTO child VALUES (0, 1);
DELETE FROM Parent WHERE id = 1;
DELETE FROM child WHERE id = 0;
DELETE FROM Parent WHERE id = 1;
DROP TABLE Parent;
DROP TABLE child;
DROP TABLE Parent;

Can also add or remove foriegn key to existing table
```
ALTER TABLE child DROP FOREIGN KEY TableName_id;
```

doesn’t work because the constraint is named, look up name with show create table

SHOW CREATE TABLE child;
ALTER TABLE child DROP FOREIGN KEY child_ibfk_1;

ALTER TABLE child ADD FOREIGN KEY (TableName_id) REFERENCES TableName(id);

Check

Can also add constraints to the values in a column

CREATE TABLE TableName (
    id INT NOT NULL,
    value INT,
    PRIMARY KEY (id),
    CHECK (value > 0)
);
INSERT INTO TableName VALUES (0, 1);
INSERT INTO TableName VALUES (1, -1);

Add and remove checks

ALTER TABLE TableName ADD CHECK (value > 0)
ALTER TABLE TableName DROP CHECK