2. SQL Database (2) Constraints, AUTO INCREMENT, Dates

SQL Constraints

: to specify rules for data in a table

Create constraints

• constraints can be specified when:
  • the table is created with the CREATE TABLE statement
  • OR after the table is created with the ALTER TABLE statement

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

SQL constraints

• used to specify rules for the data in a table
• used to limit the type of data that can go into a table
  • ensures the accuracy & reliability of the data in the table
• if there is any violation b/w the constraint and the data action, the action is aborted
• constraints can be column level or table level
• List of constraints
  • [NOT NULL](https://www.w3schools.com/sql/sql_notnull.asp) - Ensures that a column cannot have a NULL value

    • by default, a column CAN hold NULL values

      CREATE TABLE Persons (
      		ID int NOT NULL
      );

      ALTER TABLE Persons
      MODIFY COLUMN Age int NOT NULL;

  • [UNIQUE](https://www.w3schools.com/sql/sql_unique.asp) - Ensures that all values in a column are different

    • many UNIQUE constraints per table is possible

      CREATE TABLE Persons (
      		ID int NOT NULL
      		UNIQUE (ID)
      );

      ALTER TABLE Persons
      ADD UNIQUE (ID);

    • to name a UNIQUE constraint & define the constraint on multiple columns

      CREATE TABLE Persons (
      		ID int NOT NULL, 
      		LastName varchar(255) NOT NULL, 
      		CONSTRAINT UC_Person UNIQUE (ID,LastName)
      );

      ALTER TABLE Persons 
      ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

    • drop a UNIQUE constraint

      ALTER TABLE Persons
      DROP INDEX UC_Person;

  • [PRIMARY KEY](https://www.w3schools.com/sql/sql_primarykey.asp) - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

    • a table can have only one primary key but this primary key can consist of single or multiple columns

      CREATE TABLE Persons (
      		ID int NOT NULL
      		PRIMARY KEY (ID)
      );

      ALTER TABLE Persons
      ADD PRIMARY KEY (ID);

    • name the constraint

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

      ALTER TABLE Persons
      ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

    • drop the constraint

      ALTER TABLE Persons
      DROP PRIMARY KEY;

  • [FOREIGN KEY](https://www.w3schools.com/sql/sql_foreignkey.asp) - Prevents actions that would destroy links between tables

    • prevents invalid data from being inserted into the foreign key column b/c it has to be one of the values contained in the parent table

    • is a field (or collection of fields) in one table that refers to the primary key in another table

    • child table: the table with the foreign key

    • referenced (parent) table: the table with the primary key

      CREATE TABLE Orders (
      		OrderID int NOT NULL,
          OrderNumber int NOT NULL,
          PersonID int,
          PRIMARY KEY (OrderID),
          **FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)**
      );

      ALTER TABLE Orders
      ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

    • name the constraint & define it on multiple columns

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

      ALTER TABLE Orders
      ADD CONSTRAINT FK_PersonOrder
      FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

    • drop the constraint

      ALTER TABLE Orders
      DROP FOREIGN KEY FK_PersonOrder;

  • [CHECK](https://www.w3schools.com/sql/sql_check.asp) - Ensures that the values in a column satisfies a specific condition

    • limit the value range that can be placed in the column

    • column level: allow only certain values for this column

    • table level: limit the values in certain columns based on values in other columns in the row

      CREATE TABLE Persons (
          ID int NOT NULL,
          LastName varchar(255) NOT NULL,
          FirstName varchar(255),
          Age int,
          **CHECK (Age>=18)**
      );

      ALTER TABLE Persons
      ADD CHECK (Age>=18);

    • name the constraint & define it on multiple columns

      CREATE TABLE Persons (
          ID int NOT NULL,
          LastName varchar(255) NOT NULL,
          FirstName varchar(255),
          Age int,
          City varchar(255),
          **CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')**
      );

      ALTER TABLE Persons
      ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

    • drop the constraint

      ALTER TABLE Persons
      DROP CHECK CHK_PersonAge;

  • [DEFAULT](https://www.w3schools.com/sql/sql_default.asp) - Sets a default value for a column if no value is specified

    • default value will be added to all new records, if no other value is specified

      CREATE TABLE Persons (
          ID int NOT NULL,
          LastName varchar(255) NOT NULL,
          FirstName varchar(255),
          Age int,
          **City varchar(255) DEFAULT 'Sandnes'**
      );

      CREATE TABLE Orders (
          ID int NOT NULL,
          OrderNumber int NOT NULL,
          **OrderDate date DEFAULT GETDATE()**
      );

      ALTER TABLE Persons
      ALTER City SET DEFAULT 'Sandnes';

    • drop a default constraint

      ALTER TABLE Persons
      ALTER City DROP DEFAULT;

  • [CREATE INDEX](https://www.w3schools.com/sql/sql_create_index.asp) - Used to create and retrieve data from the database very quickly

    • indexes are used to retrieve data from the database

    • users cannot see the indexes

    • duplicate values are allowed by default

      CREATE INDEX *index_name*
      ON *table_name* (*column1, column2, ...*);

    • create an index that does NOT allow duplicate values

      CREATE UNIQUE INDEX *index_name*
      ON *table_name* (*column1, column2, ...*);

    • drop an index

      ALTER TABLE table_name
      DROP INDEX index_name;

---

SQL AUTO INCREMENT Field

Auto-increment

• allows a unique number to be generated automatically when a new record is inserted into a table
• this is often the primary key field that we want to be created automatically every time a new record is inserted
• no need to specify a value for the auto-increment column when inserting a new record to the table

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);
--"Personid" column is the auto-increment primary key field in the "Persons" table

• to set the starting value of the AUTO_INCREMENT sequence

  ALTER TABLE Persons AUTO_INCREMENT=100;

---

SQL Working with Dates

Date data types

Working with dates

• two dates can easily be compared if there is no time component involved
• if the query tries to find a date in a table whose date column’s format includes the time component, there will be no result returned

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

---

This post is based on W3School's articles about SQL.