INITIALLY

Oracle

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.

SQL Server

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.