Foreign Key

Keyword FOREIGN KEY

AlaSQL supports FOREIGN KEY concept.

Syntax:

    CREATE TABLE tableid (
        columnid type REFERENCES reftableid (refcolumnid,...)
    );

In this example field Orders.fruitid reference to Fruits table:

     CREATE DATABASE Fruits;
      USE DATABASE Fruits;
      CREATE TABLE Fruits (
        fruitid INT PRIMARY KEY,
        fruitname NVARCHAR(MAX),
        price MONEY
      );

      CREATE TABLE Orders (
        orderid INT PRIMARY KEY IDENTITY,
        fruitid INT REFERENCES Fruits(fruitid),
        qty FLOAT
      );

ON DELETE / ON UPDATE Actions

AlaSQL supports parsing of the following ON DELETE and ON UPDATE actions to ensure SQL compatibility:

• NO ACTION (default)
• CASCADE
• SET NULL
• SET DEFAULT
• RESTRICT

Note: Currently, AlaSQL parses these clauses to support standard SQL syntax (SQL-99 compatibility), but does not yet enforce the logic for any of them.

• NO ACTION: No check is performed (effectively allows the delete/update).
• CASCADE: No cascading delete/update is performed.
• SET NULL: No values are set to NULL.
• SET DEFAULT: No values are set to DEFAULT.
• RESTRICT: No check is performed (effectively allows the delete/update).

This means deleting a row in a parent table will not automatically delete rows in the child table, nor will it stop you from deleting a parent row that has children.

    CREATE TABLE Orders (
        orderid INT PRIMARY KEY IDENTITY,
        fruitid INT REFERENCES Fruits(fruitid) ON DELETE CASCADE ON UPDATE CASCADE,
        qty FLOAT
    );

You can also specify different actions for DELETE and UPDATE:

    CREATE TABLE Orders (
        orderid INT PRIMARY KEY IDENTITY,
        fruitid INT REFERENCES Fruits(fruitid) ON DELETE SET NULL ON UPDATE CASCADE,
        qty FLOAT
    );

See also: [PRIMARY KEY](Primary Key)