Constraints
YSQL allows you to define primary and foreign keys, as well as check values based on various criteria.
Primary Key
A primary key is a means to identify a specific row in a table via one or more columns. To define a primary key, you create a constraint that is, functionally, a unique index applied to the table columns.
Most commonly, the primary key is added to the table when the table is created, as demonstrated by the following syntax:
CREATE TABLE (
column1 data_type PRIMARY KEY,
column2 data_type,
column3 data_type,
…
);
Suppose you work with a database that requires the following table to be created:
CREATE TABLE employees (
employee_no integer PRIMARY KEY,
name text,
department text
);
The primary key of the employees
table is employee_no
, which uniquely identifies an employee.
The following syntax the primary key definition for more than one column:
CREATE TABLE (
column1 data_type,
column2 data_type,
column3 data_type,
…
PRIMARY KEY (column1, column2)
);
The following example creates the employees
table in which the primary key is a combination of employee_no
and name
:
CREATE TABLE employees (
employee_no integer,
name text,
department text,
PRIMARY KEY (employee_no, name)
);
YSQL assigns a default name in the format tablename_pkey
to the primary key constraint. In the preceding example, it is employees_pkey
. If you need a different name, you can specify it using the CONSTRAINT
clause, as per the following syntax:
CONSTRAINT constraint_name PRIMARY KEY(column1, column2, ...);
In some cases you might decide to define a primary key for an existing table. To do this, you use the ALTER TABLE
statement, as per the following syntax:
ALTER TABLE table_name ADD PRIMARY KEY (column1, column2);
The ALTER TABLE
statement also allows you to add an auto-incremented primary key to an existing table by using the SERIAL type, as per the following syntax:
ALTER TABLE table_name ADD COLUMN ID SERIAL PRIMARY KEY;
You can remove a primary key constraint by using the ALTER TABLE
statement, as demonstrated by the following syntax:
ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint;
The following example shows how to remove the primary key constraint from the employees
table:
ALTER TABLE employees DROP CONSTRAINT employees_pkey;
For more information and examples, refer to the following:
Foreign Key
A foreign key represents one or more columns in a table referencing the following:
- A primary key in another table.
- A unique index or columns restricted with a unique constraint in another table.
Tables can have multiple foreign keys.
You use a foreign key constraint to maintain the referential integrity of data between two tables: values in columns in one table equal the values in columns in another table.
You define the foreign key constraint using the following syntax:
[CONSTRAINT fk_name]
FOREIGN KEY(fk_columns)
REFERENCES parent_table(parent_key_columns)
[ON DELETE delete_action]
[ON UPDATE update_action]
Defining the CONSTRAINT
clause and naming the foreign key is optional. If you omit it, an auto-generated name is provided by YSQL. The REFERENCES
clause specifies the parent table and its columns referenced by the fk_columns. Defining actions is also optional; if defined, they determine the behaviors when the primary key in the parent table is deleted or updated. YSQL allows you to perform the following actions:
SET NULL
- when the referenced rows in the parent table are deleted or updated, foreign key columns in the referencing rows of the child table are automatically set toNULL
.SET DEFAULT
- when the referenced rows of the parent table are deleted or updated, the default value is set to the foreign key column of the referencing rows in the child table.RESTRICT
- when the referenced rows in the parent table are deleted or updated, deletion of a referenced row is prevented.CASCADE
- when the referenced rows in the parent table are deleted or updated, the referencing rows in the child table are deleted or updated.NO ACTION
(default) - when the referenced rows in the parent table are deleted or updated, no action is taken.
The following example creates two tables:
CREATE TABLE employees(
employee_no integer GENERATED ALWAYS AS IDENTITY,
name text NOT NULL,
department text,
PRIMARY KEY(employee_no)
);
CREATE TABLE contacts(
contact_id integer GENERATED ALWAYS AS IDENTITY,
employee_no integer,
contact_name text NOT NULL,
phone integer,
email text,
PRIMARY KEY(contact_id),
CONSTRAINT fk_employee
FOREIGN KEY(employee_no)
REFERENCES employees(employee_no)
);
In the preceding example, the parent table is employees
and the child table is contacts
. Each employee has any number of contacts, and each contact belongs to no more than one employee. The employee_no
column in the contacts
table is the foreign key column that references the primary key column with the same name in the employees
table. The fk_customer
foreign key constraint in the contacts
table defines the employee_no
as the foreign key. Since fk_customer
is not associated with any action, NO ACTION
is applied by default.
The following example shows how to create the same contacts
table with a CASCADE
action ON DELETE
:
CREATE TABLE contacts(
...
REFERENCES employees_1(employee_no)
ON DELETE CASCADE
);
YSQL enables you to ads a foreign key constraint to an existing table by using the ALTER TABLE
statement, as demonstrated by the following syntax:
ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (fk_columns)
REFERENCES parent_table (parent_key_columns);
Before altering a table with a foreign key constraint, you need to remove the existing foreign key constraint, as per the following syntax:
ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;
The next step is to add a new foreign key constraint, possibly including an action, as demonstrated by the following syntax:
ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
[ON DELETE action];
For more information and examples, see the following:
CHECK Constraint
The YSQL CHECK
constraints allow you to constrain values in columns based on a boolean expression. The values are evaluated with regards to meeting a specific requirement before these values are inserted or updated; if they fail the check, YSQL rejects the changes and displays a constraint violation error.
In most cases, you add the CHECK
constraint when you create a table, as demonstrated by the following example:
CREATE TABLE employees (
employee_no integer PRIMARY KEY,
name text,
department text,
birth DATE CHECK (birth > '1940-01-01'),
salary numeric CHECK (salary > 10)
);
The following example attempts to insert a row that violates the CHECK
constraint into the employees
table:
INSERT INTO employees (employee_no, name, department, birth, salary)
VALUES (2001, 'Hugh Grant', 'Sales', '1963-05-05', 0);
The following output shows that the execution of the INSERT
statement failed because of the CHECK
constraint on the salary
column which only accepts values greater than 10:
ERROR: new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL: Failing row contains (2001, Hugh Grant, Sales, 1963-05-05, 0).
The preceding output shows the name of the CHECK
constraint as employees_salary_check
which was assigned by default based on the table_column_check pattern. If you need a specific name for the CHECK
constraint, you can set it, as per the following example:
(
...
salary numeric CONSTRAINT fair_salary CHECK (salary > 10)
...
);
YSQL also allows you to add CHECK
constraints to existing tables by using the ALTER TABLE
statement. The following example shows how to add a length check for the employee name in the employees
table:
ALTER TABLE employees
ADD CONSTRAINT name_check CHECK (char_length(name) <= 3);
For additional examples, see Table with CHECK constraint.
UNIQUE Constraint
The UNIQUE
constraint allows you to ensure that values stored in columns are unique across rows in a table. During inserting new rows or updating existing ones, the UNIQUE
constraint checks if the value is already in the table, in which case the change is rejected and an error is displayed.
When you add a UNIQUE
constraint to one or more columns, YSQL automatically creates a unique index on these columns.
The following example creates a table with a UNIQUE
constraint for the phone
column:
CREATE TABLE employees (
employee_no integer PRIMARY KEY,
name text,
department text,
phone integer UNIQUE
);
The following example creates the same constraint for the same column of the same table, only as a table constraint:
CREATE TABLE employees (
employee_no integer PRIMARY KEY,
name text,
department text,
phone integer,
UNIQUE(phone)
);
The following example creates a UNIQUE
constraint on a group of columns in a new table:
CREATE TABLE employees (
employee_no integer PRIMARY KEY,
name text,
department text,
phone integer,
email text
UNIQUE(phone, email)
);
For additional examples, see Table with UNIQUE constraint.
NOT NULL Constraint
YSQL provides a NOT NULL
constraint as a means to control whether or not a column can accept NULL
values. If a column has a NOT NULL
constraint set, any attempt to insert a NULL
value or update it with a NULL
value results in an error.
For additional information and examples, see the following: