Sql server: check constraints

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref


Using an ALTER TABLE statement

The syntax for creating a check constraint in an ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
  CHECK (column_name condition);
table_name
The name of the table that you wish to modify by adding a check constraint.
constraint_name
The name to assign to the check constraint.
column_name
The column in the table that the check constraint applies to.
condition
The condition that must be met for the check constraint to succeed.

Example

Let’s look at an example of how to use the ALTER TABLE statement to create a check constraint in SQL Server.

For example:

ALTER TABLE employees
ADD CONSTRAINT check_last_name
  CHECK (last_name IN ('Smith', 'Anderson', 'Jones'));

SQL Server Check Constraints

1. Check constraint using a CREATE TABLE statement

CREATE TABLE table_name ( column1 datatype, column2 datatype, ……………….. column_namen datatype, CONSTRAINT constraint_name CHECK (column_name condition) )

Example

Check constraint using CREATE TABLE statement in SQL server on table titled ‘Students’ CREATE TABLE Students ( Stud_id INT NOT NULL, Last_name VARCHAR(50) NOT NULL, First_name VARCHAR(50), CONSTRAINT check_Stud_id CHECK (Stud_id BETWEEN 1 and 100) )

2. Check constraint using CREATE TABLE statement

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition)

Example:

Example

ALTER TABLE Students ADD CONSTRAINT Check_First_name CHECK (First_name IN (‘SAM’, ‘JON’))

3. Check constraint using DROP TABLE statement

Example

Check constraint using DROP TABLE statement in SQL server on table titled ‘Students’. ALTER TABLE Students DROP CONSTRAINT First_name

Primary key

  • Primary key uniquely identifies each row in the table.
  • When multiple columns are used as a primary key, it is known as composite primary key.
  • A primary key should not have null value. Each table can have only one primary key.
  • A primary key should be defined in a CREATE TABLE statement or ALTER TABLE statement.

1. PRIMARY KEY using the CREATE TABLE statement in SQL server

CREATE TABLE table_name ( column_name1 datatype PRIMARY KEY, column_name2 datatype, ……………….. column_namen datatype )OR CREATE TABLE table_name ( column1 datatype, column2 datatype, ……………….. column_namen datatype CONSTRAINT constraint_name PRIMARY KEY(column1, column2, ….,column_n) )

Example 1:

CREATE TABLE Students ( Stud_id INT NOT NULL PRIMARY KEY, Last_name VARCHAR(50) , First_name VARCHAR(50) )

Example 2:for multiple columns

CREATE TABLE Students ( Stud_id INT, Last_name VARCHAR(50) , First_name VARCHAR(50) CONSTRAINT students_pk PRIMARY KEY(Last_name, First_name) )

2. PRIMARY KEY using the ALTER TABLE statement in SQL server

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2,…., Column_n)

Example:

ALTER TABLE Students ADD CONSTRAINT students_pk PRIMARY KEY (Last_name, First_name)

3. DROP Primary key using the ALTER TABLE statement in SQL Server

ALTER TABLE table_name DROP CONSTRAINT constraint_name

Example:

ALTER TABLE Students DROP CONSTRAINT students_pk

4. Disable a Primary key in SQL server

ALTER INDEX constraint_name ON table_name DISABLE

ALTER INDEX last_name_pk ON Students DISABLE

5. Enable a Primary key in SQL server

Описание

Уникальное ограничение — это отдельное поле или комбинация полей, однозначно определяющая запись. Некоторые поля могут содержать нулевые значения, если комбинация значений является уникальной.

В чем разница между уникальным ограничением и первичным ключом?

Первичный ключ Уникальное ограничение
«Ни одно из полей, являющихся частью первичного ключа, не может содержать значение NULL.

(Хотя стандарты SQL-89 и SQL-92 не допускают значения NULL в первичном ключе, SQLite допускает NULL при определенных обстоятельствах. Мы настоятельно рекомендуем не использовать значение NULL в первичном ключе.)»

Некоторые поля, являющиеся частью ограничения уникальности, могут содержать значения NULL, если комбинация значений уникальна.

Introduction to Oracle Check constraint

An Oracle check constraint allows you to enforce domain integrity by limiting the values accepted by one or more columns.

To create a check constraint, you define a logical expression that returns true or false. Oracle uses this expression to validate the data that is being inserted or updated. If the expression evaluates to true, Oracle accepts the data and carry the insert or update. Otherwise, Oracle will reject the data and does not insert or update at all.

You can apply a check constraint to a column or a group of columns. A column may have one or more check constraints.

When you apply multiple check constraints to a column, make sure that they are not mutually exclusive. In addition, you should not assume any particular order of evaluation of the expressions.

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric


SQL OperatorsSQL Data TypesSQL Quick Ref

Introduction to the MySQL CHECK constraint

Prior to MySQL 8.0.16, the allows you to include a table constraint. However, the constraint is just parsed and ignored:

As of MySQL 8.0.16, the supported essential features of table and column constraints for all storage engines.

Here is the syntax:

In this syntax:

First, specify the name for the check constraint that you want to create. If you omit the constraint name, MySQL automatically generates a name with the following convention:

where is an ordinal number 1,2,3… For example, the names of constraints for the table will be , , …

Second, specify a Boolean which must evaluate to or for each row of the table. If the expression evaluates to , the values violate the constraint or a constraint violation occurs.

Third, optionally specify enforcement clause to indicate whether the check constraint is enforced:

  • Use or just omit the clause to create and enforce the constraint.
  • Use to create the constraint but do not enforce it.

As mentioned earlier, you can specify a constraint as a table constraint or column constraint.

A table constraint can reference multiple columns while the column constraint can refer to the only column where it is defined.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the «Age» column when the «Persons» table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

MySQL:

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

SQL Server / Oracle / MS Access:

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

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE table_name
(
  column1 datatype ,
  column2 datatype ,

  ...

  CONSTRAINT constraint_name
    CHECK  (column_name condition)

);
table_name
The name of the table that you wish to create with a check constraint.
constraint_name
The name to assign to the check constraint.
column_name
The column in the table that the check constraint applies to.
condition
The condition that must be met for the check constraint to succeed.

Example

Let’s look at an example of how to use the CREATE TABLE statement in SQL Server to create a check constraint.

For example:

CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT check_employee_id
    CHECK (employee_id BETWEEN 1 and 10000)
);

In this first example, we’ve created a check constraint on the employees table called check_employee_id. This constraint ensures that the employee_id field contains values between 1 and 10000.

Let’s take a look at another example.

CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT check_salary
    CHECK (salary > 0)
);

Добавить уникальное ограничение

Если ваша таблица уже существует и вы хотите добавить уникальное ограничение позже, вы не можете использовать оператор ALTER TABLE, чтобы добавить уникальное ограничение. Вместо этого вы должны создать новую таблицу с уникальным ограничением и скопировать данные в эту новую таблицу.

Синтаксис

Синтаксис для добавления уникального ограничения к таблице в SQLite:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name ( column1 datatype , column2 datatype , … CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, … uc_col_n) );

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

table_name Имя таблицы для изменения. Это таблица, к которой вы хотите добавить уникальное ограничение.

old_table Название оригинальной таблицы. Оно останется после того, как вы создадите новую таблицу с добавленным уникальным ограничением.

constraint_name Имя уникального ограничения.

uc_col1, uc_col2, … uc_col_n Столбцы, которые составляют уникальное ограничение.

Пример

Рассмотрим пример того, как добавить уникальное ограничение к существующей таблице в SQLite. Скажем так, у нас уже есть таблица products со следующим определением:

PgSQL

CREATE TABLE products ( product_id INTEGER PRIMARY KEY AUTOINCREMENT, product_name VARCHAR NOT NULL, quantity INTEGER NOT NULL DEFAULT 0 );

1 2 3 4 5

CREATETABLEproducts (product_idINTEGERPRIMARYKEYAUTOINCREMENT,

product_nameVARCHARNOT NULL,

quantityINTEGERNOT NULLDEFAULT0 );

И мы хотели добавить уникальное ограничение в таблицу products, которая состоит из product_name. Мы могли бы запустить следующие команды:

PgSQL

PRAGMA foreign_keys=off;


BEGIN TRANSACTION;

ALTER TABLE products RENAME TO old_products;

CREATE TABLE products ( product_id INTEGER PRIMARY KEY AUTOINCREMENT, product_name VARCHAR NOT NULL, quantity INTEGER NOT NULL DEFAULT 0, CONSTRAINT product_name_unique UNIQUE (product_name) );

INSERT INTO products SELECT * FROM old_products;

COMMIT;

PRAGMA foreign_keys=on;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

PRAGMAforeign_keys=off;  

BEGINTRANSACTION;  

ALTERTABLEproductsRENAMETOold_products;  

CREATETABLEproducts (product_idINTEGERPRIMARYKEYAUTOINCREMENT,

product_nameVARCHARNOT NULL,

quantityINTEGERNOT NULLDEFAULT0,

CONSTRAINTproduct_name_uniqueUNIQUE(product_name) );  

INSERTINTOproductsSELECT*FROMold_products;  

COMMIT;   PRAGMAforeign_keys=on;

В этом примере мы создали уникальное ограничение для таблицы products с именем product_name_unique, которое состоит из столбца product_name. Исходная таблица все еще будет существовать в базе данных с именем old_products. Вы можете удалить таблицу old_products после того, как убедитесь, что таблица products и данные соответствуют ожиданиям.

PgSQL

DROP TABLE old_products;

1 DROPTABLEold_products;

Creating Check constraint syntax

Typically, you create a check constraint on a column when you create the table:

In this syntax, a check constraint consists of the keyword followed by an expression in parentheses. The expression should always involve the column thus constrained. Otherwise, the check constraint does not make any sense.

If you want to assign the check constraint an explicit name, you use the clause below:

When a check constraint is on the same line as the table column, its syntax is called in-line constraint.

In addition, you can use the out-of-line constraint syntax as follows:

Creating Oracle Check constraint examples

The following example creates the table whose buy prices are positive:

Attempting to insert 0 or negative buy price will cause an error:

Oracle issued the following error:

In this error message, is the name of the check constraint assigned by Oracle and is the schema name.

To better analyzing the error message and to refer to the constraint later, you can give a check constraint an explicit name:

Now, if you try to insert a part with a negative price:

The error message is more precise:


С этим читают