SQL Reference - INITIALLY

INITIALLY

The SQL INITIALLY keyword is used in conjunction with the DEFERRABLE attribute in the declaration of foreign key constraints. It controls when the system should check the constraint. With INITIALLY IMMEDIATE, validation is performed immediately at the end of each statement. With INITIALLY DEFERRED, validation is deferred until the end of the transaction.

Example

CREATE TABLE persons (
    person_id INT,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    city VARCHAR(255),
    CONSTRAINT pk_persons PRIMARY KEY (person_id)
) INITIALLY DEFERRED;

Output

Table PERSONS created.

Explanation

In this example, the INITIALLY DEFERRED option is used in the CREATE TABLE statement. It creates a table named ‘persons’ with some columns. INITIALLY DEFERRED ensures that all constraints (like the PRIMARY KEY constraint here) are checked only at the transaction’s end, not after every statement. While this example does not demonstrate deferral in action, it sets up a condition where it could be seen if multiple operations were performed within a transaction.

Example

CREATE TABLE Employees (
  ID int,
  Name nvarchar(50),
  Salary int,
  StartDate date,
  City nvarchar(50)
);

INSERT INTO Employees
VALUES (1, 'John', 10000, '2021-01-01', 'New York');

SELECT * FROM Employees;

Output

ID   Name    Salary  StartDate   City
--   ----    ------  ---------   ----
1    John    10000   2021-01-01  New York

Explanation

The example provided illustrates the creation of a table “Employees” with five columns: ID, Name, Salary, StartDate and City in SQL Server. A row of data is then inserted into the table, featuring the values 1, ‘John’, 10000, ‘2021-01-01’, ‘New York’ for each field respectively. The final command then retrieves all records from the Employees table, which in this case is just the single row of data that was entered.