Ограничения oracle

Create Primary Key — Using ALTER TABLE statement

You can create a primary key in Oracle with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

Let’s look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we’ve created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

Using a CREATE TABLE statement

Syntax

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE SET NULL
);

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10),
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE SET NULL
);

In this example, we’ve created a primary key on the supplier table called supplier_pk. It consists of only one field — the supplier_id field. Then we’ve created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

Because of the set null on delete, when a record in the supplier table is deleted, all corresponding records in the products table will have the supplier_id values set to null.

We could also create a foreign key «set null on delete» with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10),
  supplier_name varchar2(50),
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
    ON DELETE SET NULL
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields — the supplier_id and supplier_name fields.

sql — Внешние ключи TSQL для просмотров?

Итак, первые две таблицы; A = Отдел B = Сотрудник

Теперь я подброшу некоторые данные в

Итак, теперь я создам представление для первой таблицы, чтобы отфильтровать некоторые отделы.

Это возвращает

И в вашем примере я добавлю представление для второй таблицы, которая ничего не фильтрует.

Это возвращает

Мне кажется, что вы думаете, что Employee No 5, DepartmentID = 3 указывает на никуда?

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

Возвращает

Итак, здесь ничего не сломалось, соединение просто не нашло совпадающих записей для Это на самом деле то же самое, что если бы я присоединил таблицы, а затем включил фильтр как в первом представлении:

Возврат снова:

В обоих случаях объединения не терпят неудачу, они просто делают так, как ожидалось.

Теперь я попытаюсь сломать ссылочную целостность через представление (нет DepartmentID = 127)

И это приводит к:

Если я попытаюсь удалить отдел через представление

Результат:

Так что ограничения для базовых таблиц все еще применяются.

Надеюсь, что это поможет, но тогда, возможно, я неправильно понял вашу проблему.

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

SQL Справочник

SQL Ключевые слова
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 Функции
Функции строк
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
Функции чисел
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
Функции дат
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
Функции расширений
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 функции
Функции строк
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
Функции чисел
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
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

MySQL FOREIGN KEY syntax

Here is the basic syntax of defining a foreign key constraint in the or statement:

In this syntax:

First, specify the name of foreign key constraint that you want to create after the keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.

Second, specify a list of comma-separated foreign key columns after the keywords. The foreign key name is also optional and is generated automatically if you skip it.

Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.

Finally, specify how foreign key maintains the referential integrity between the child and parent tables by using the and clauses. The determines action which MySQL will take when values in the parent key columns are deleted () or updated ().

MySQL has five reference options: , , , , and .

: if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated.
: if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to .
: if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.
: is the same as .
: is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.

In fact, MySQL fully supports three actions: , and .

If you don’t specify the and clause, the default action is .

Using a CREATE TABLE statement

Syntax

The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE CASCADE
);

Example

Let’s look at an example of how to create a foreign key with cascade delete using the CREATE TABLE statement in Oracle/PLSQL.

For example:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE CASCADE
);

Because of the cascade delete, when a record in the supplier table is deleted, all records in the products table will also be deleted that have the same supplier_id value.

We could also create a foreign key (with a cascade delete) with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
    ON DELETE CASCADE
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields — the supplier_id and supplier_name fields.

Create Primary Key — Using CREATE TABLE statement

You can create a primary key in Oracle with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

Let’s look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we’ve created a primary key on the supplier table called supplier_pk. It consists of only one field — the supplier_id field.

We could also create a primary key with more than one field as in the example below:

Создание внешнего ключа — использование оператора CREATE TABLE

Синтаксис

Синтаксис создания внешнего ключа с помощью оператора CREATE TABLE в SQL Server (Transact-SQL):

CREATE TABLE child_table ( column1 datatype , column2 datatype , …

CONSTRAINT fk_name FOREIGN KEY (child_col1, child_col2, … child_col_n) REFERENCES parent_table (parent_col1, parent_col2, … parent_col_n) );

child_table — имя дочерней таблицы, которую вы хотите создать.column1, column2 — столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.fk_name — имя ограничения внешнего ключа, которое вы хотите создать.child_col1, child_col2, … child_col_n — столбцы в child_table, которые будут ссылаться на первичный ключ в parent_table (родительской таблице).parent_table — имя родительской таблицы, первичный ключ которой будет использоваться в child_table.parent_col1, parent_col2, … parent_col3 — столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1, child_col2, … child_col_n в child_table.ON DELETE — необязательный. Он определяет, что делать с дочерними данными при удалении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.ON UPDATE — необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.NO ACTION — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.CASCADE — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.SET NULL — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.SET DEFAULT — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.

SQL Справочник

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Как добавить внешний ключ в существующую таблицу

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

Синтаксис

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

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 ( column1 datatype , column2 datatype , …

CONSTRAINT fk_column FOREIGN KEY (column1, column2, … column_n) REFERENCES parent_table (column1, column2, … column_n) );

INSERT INTO table1 SELECT * FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Пример

Во-первых, давайте начнем с создания наших 2 таблиц (departments и employees):

PgSQL

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
department_name VARCHAR
);

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
department_id INTEGER
);

1
2
3
4
5
6
7
8
9
10
11

CREATETABLEdepartments
(department_idINTEGERPRIMARYKEYAUTOINCREMENT,

department_nameVARCHAR

);

CREATETABLEemployees
(employee_idINTEGERPRIMARYKEYAUTOINCREMENT,

last_nameVARCHARNOT NULL,

first_nameVARCHAR,

department_idINTEGER

);

Далее добавим в эти таблицы некоторые данные:

PgSQL

INSERT INTO departments VALUES (30, ‘IT’);
INSERT INTO departments VALUES (999, ‘Sales’);

INSERT INTO employees VALUES (10000, ‘Smith’, ‘Samanta’, 30);
INSERT INTO employees VALUES (10001, ‘Anderson’, ‘Gans’, 999);

1
2
3
4
5

INSERTINTOdepartmentsVALUES(30,’IT’);

INSERTINTOdepartmentsVALUES(999,’Sales’);

INSERTINTOemployeesVALUES(10000,’Smith’,’Samanta’,30);

INSERTINTOemployeesVALUES(10001,’Anderson’,’Gans’,999);

Теперь давайте добавим внешний ключ в таблицу employees:

PgSQL

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
department_id INTEGER,
CONSTRAINT fk_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

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

PRAGMAforeign_keys=off;

BEGINTRANSACTION;

ALTERTABLEemployeesRENAMETO_employees_old;

CREATETABLEemployees
(employee_idINTEGERPRIMARYKEYAUTOINCREMENT,

last_nameVARCHARNOT NULL,

first_nameVARCHAR,

department_idINTEGER,

CONSTRAINTfk_departments

FOREIGNKEY(department_id)

REFERENCESdepartments(department_id)
);

INSERTINTOemployeesSELECT*FROM_employees_old;

COMMIT;

PRAGMAforeign_keys=on;

Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с внешним ключом fk_departments, который ссылается на таблицу departments на основе поля department_id. Затем он вставит все данные из таблицы _employees_old в таблицу employees.

Этот альтернативный путь позволяет добавить внешний ключ в таблицу employees, не теряя данные в таблице.

Introduction to Oracle foreign key constraint

A foreign key is all about the relationship. Let’s start with an example to clearly understand its concept.

Suppose, we have two tables and :

The table stores supplier groups e.g., one-time supplier, third-party supplier, and inter-co supplier. Each supplier group may have zero, one, or many suppliers.

The table stores the supplier information. Each supplier must belong to a supplier group.

The relationship between and table is one-to-many. In other words, one supplier group has many suppliers while each supplier must belong to a supplier group.

The in the table is used for establishing the relationship between rows in the and tables.

Before inserting a row into the table, you have to look up an existing in the table and use this value for insertion.

Assuming that the table contains the following data:

To insert a new third-party supplier, you have to use the 2 as follows:

It works perfectly fine. However, the following statement also works:

The table has no row with group id 4 but nothing prevents you from inserting it into the table, which is an issue.

For example, the following query fails to get all suppliers and their groups:

As you can see, the supplier is missing in the result set.

A solution to fix this problem is to use the Oracle foreign key constraint to enforce the relationship between rows from the and tables.

First, drop the table:

Second, recreate the table with a foreign key constraint:

In this statement, the following clause was newly added:

This clause defines the column in the table as a foreign key that references to the column of the table.

This way, the constraint is enforced by Oracle. In other words, attempting to insert a row into the table that does not correspond to any row in the table will fail, as attempting to delete a row from the table where there exist dependent rows in the table.

The table is called a child table while the is referred to as a parent table. To extend the parent-child analogy, the primary key value is taken from the parent table () and is inserted into the child table () i.e., the child carries a as the parent’s DNA or genetic code.

By the way, the concept of referential integrity is all about maintaining and enforcing this parent-child relationship.

Oracle foreign key constraint in actions

The following statement works because the table has a row with 1:

However, the following statement will fail:

Because the has no row with id 4. The following is the error message:

Similarly, attempting to delete a row with 1 in the table will fail:

Oracle issued the following error message:

Because the table (child table) has a row that references to the row which is being deleted.