This chapter explains how to use constraints to enforce the business rules associated with your database and prevent the entry of invalid information into tables. Show
Topics:
Overview of ConstraintsYou can define constraints to enforce business rules on data in your tables. Business rules specify conditions and relationships that must always be true, or must always be false. Because each company defines its own policies about things like salaries, employee numbers, inventory tracking, and so on, you can specify a different set of rules for each database table. When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that modifies data in the table, Oracle Database ensures that the new data satisfies the integrity constraint, without any checking within your program. Enforcing Business Rules with ConstraintsYou can enforce rules by defining constraints more reliably than by adding logic to your application. Oracle Database can check that all the data in a table obeys an integrity constraint faster than an application can. For example, to ensure that each employee works for a valid department:
When you add an employee record to the table, Oracle Database automatically checks that its department number appears in the department table. To enforce this rule without constraints, you can use a trigger to query the department table and test that each employee's department is valid. This method is less reliable than using constraints, because Enforcing Business Rules with Application LogicYou might enforce business rules through both application logic and constraints, if you can filter out bad data before attempting an insert or update. This might let you provide instant feedback to the user, and reduce the load on the database. This technique is appropriate when you can determine that data values are wrong or out of range without checking against any data in the table. Creating Indexes for Use with ConstraintsAll enabled unique and primary keys require corresponding indexes. Create these indexes by hand, rather than letting the database create them. Note that:
When to Use NOT NULL ConstraintsBy default, all columns can contain null values. Define
Because Oracle Database indexes do not store keys that are all null, to allow
index-only scans of the table or some other operation that requires indexing all rows, you must put a Specify a ALTER TABLE table_name MODIFY column_name NOT NULL; Example 5-1 uses the SQL*Plus command Example 5-1 Inserting NULL Values into Columns with NOT NULL Constraints DESCRIBE DEPARTMENTS; Result: Name Null? Type ----------------------------------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) Try to insert INSERT INTO DEPARTMENTS ( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID ) VALUES (NULL, 'Sales', 200, 1700); Result: VALUES (NULL, 'Sales', 200, 1700) * ERROR at line 4: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID") Omitting a value for a column that cannot be INSERT INTO DEPARTMENTS ( DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID ) VALUES ('Sales', 200, 1700); Result: INSERT INTO DEPARTMENTS ( * ERROR at line 1: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID") When to Use Default Column ValuesAssign default values to columns that contain typical values. For example, in the Default values can help avoid errors where there is a number, such as zero, that applies to a column that has no entry. For example, a default value of zero can simplify testing, by changing a test like this: IF sal IS NOT NULL AND sal < 50000 to the simpler form: IF sal < 50000 Depending upon your business rules, you might use default values to represent zero or false, or leave the default values as Defaults are also useful when you use a view to make a subset of a table's columns visible. For example, you might allow users to insert rows through a view. The base table might also have a column named CREATE TABLE audit_trail (
value1 NUMBER,
value2 VARCHAR2(32),
inserter VARCHAR2(30) DEFAULT USER);
Setting Default Column ValuesDefault values can be defined using any literal, or almost any expression, including calls to these functions:
Default values cannot include expressions that refer to a sequence, PL/SQL function, column, Sometimes the default value is the result of a SQL function. For example, a call to If you do not explicitly define a default value for a column, the default for the column is implicitly set to You can use the keyword Choosing a Primary Key for a TableEach table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist. When selecting a primary key, use these guidelines:
When to Use UNIQUE ConstraintsChoose columns for unique keys carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. Figure 5-1 shows an example of a table with a unique key constraint. Note: You cannot have identical values in the non-null columns of a composite Some examples of good unique keys include:
When to Use Constraints On ViewsThe constraints in this chapter apply to tables, not views. Although you can declare constraints on views, such constraints do not help maintain data integrity. Instead, they are used to enable query rewrites on queries involving views, which helps performance with materialized views and other data warehousing features. Such constraints are always
declared with the Enforcing Referential Integrity with ConstraintsWhenever two tables contain one or more common columns, Oracle Database can enforce the relationship between the two tables through a referential integrity constraint. Define a Note: In static data dictionary views Figure 5-2 shows a foreign key defined on the department number. It guarantees that every value in this column must match a value in the primary key of the department table. This constraint prevents erroneous department numbers from getting into the employee table. Foreign keys can be composed of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same data types. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns. FOREIGN KEY Constraints and NULL ValuesForeign keys allow key values that are all
Defining Relationships Between Parent and Child TablesSeveral relationships between parent and child tables can be determined by the other types of constraints defined on the foreign key in the child table. No Constraints on the Foreign Key When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key. This model establishes a one-to-many relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. An example of such a relationship is shown in
Figure 5-2 between the NOT NULL Constraint on the Foreign Key When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key. Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section illustrates such a relationship. However, in this case, employees must have a reference to a specific department. UNIQUE Constraint on the Foreign Key When a This model establishes a one-to-one relationship between the parent
and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the employee table had a column named
UNIQUE and NOT NULL Constraints on the Foreign Key When both This model establishes a one-to-one relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. If you expand the previous example by adding a Rules for Multiple FOREIGN KEY ConstraintsOracle Database allows a column to be referenced by multiple Deferring Constraint ChecksWhen Oracle Database checks a constraint, it signals an error if the constraint is not satisfied. To defer checking constraints until the end of the current transaction, use the Note: You cannot use the When deferring constraint checks:
In Example
5-2, the Example 5-2 Deferring Constraint Checks DROP TABLE dept; CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, dname VARCHAR2 (30) ); DROP TABLE emp; CREATE TABLE emp ( empno NUMBER, ename VARCHAR2(30), deptno NUMBER, CONSTRAINT pk_emp_empno PRIMARY KEY (empno) DEFERRABLE, CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) DEFERRABLE ); INSERT INTO dept (deptno, dname) VALUES (10, 'Accounting'); INSERT INTO dept (deptno, dname) VALUES (20, 'SALES'); INSERT INTO emp (empno, ename, deptno) VALUES (1, 'Corleone', 10); INSERT INTO emp (empno, ename, deptno) VALUES (2, 'Costanza', 20); COMMIT; SET CONSTRAINTS ALL DEFERRED; UPDATE dept SET deptno = deptno + 10 WHERE deptno = 20; Query: SELECT * from dept ORDER BY deptno; Result: DEPTNO DNAME ---------- ------------------------------ 10 Accounting 30 SALES 2 rows selected. Update: UPDATE emp SET deptno = deptno + 10 WHERE deptno = 20; Result: 1 row updated. Query: SELECT * from emp ORDER BY deptno; Result: EMPNO ENAME DEPTNO ---------- ------------------------------ ---------- 1 Corleone 10 2 Costanza 30 2 rows selected. The Minimizing Space and Time Overhead for Indexes Associated with ConstraintsWhen you create a When Oracle Database uses a unique index to enforce a constraint, and constraints associated with the unique index are dropped or disabled, the index is dropped. To preserve the statistics associated with the index (which would take a long time to re-create), specify the While enabled foreign keys reference a Note:
To use existing indexes when creating unique and primary key constraints, include Guidelines for Indexing Foreign KeysIndex foreign keys unless the matching unique or primary key is never updated or deleted. Referential Integrity in a Distributed DatabaseThe declaration of a referential constraint cannot specify a foreign key that references a primary or unique key of a remote table. However, you can maintain parent/child table relationships across nodes using triggers. Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can make both the parent table and the child table inaccessible. For example, assume that the child table is in the If the network connection between the two databases fails, then some data manipulation language (DML) statements against
the child table (those that insert rows or update a foreign key value) cannot proceed, because the referential integrity triggers must have access to the parent table in the When to Use CHECK ConstraintsUse Examples of
Restrictions on CHECK ConstraintsA
See Also:
Designing CHECK ConstraintsWhen using For example, consider this CHECK (Sal > 0 OR Comm >= 0) At first glance, this rule may be interpreted as "do not allow a row in the employee table unless the employee salary is greater than zero or the employee commission is greater than or equal to zero." But if a row is inserted with a null salary, that row does not violate the
Rules for Multiple CHECK ConstraintsA single column can have multiple The order in which the constraints are evaluated is not defined, so be careful not to rely on the order or to define multiple constraints that conflict with each other. Choosing Between CHECK and NOT NULL ConstraintsAccording to the ANSI/ISO standard, a CHECK (column_name IS NOT NULL)
Therefore, you can write In the case where a composite key can allow only all nulls or all values, you must use a CHECK ((C1 IS NULL AND C2 IS NULL) OR (C1 IS NOT NULL AND C2 IS NOT NULL)) Examples of Defining ConstraintsExample 5-3 and Example 5-4 show how to create simple constraints during the prototype phase of your database design. In these examples, each constraint is given a name. Naming the constraints prevents the database from creating multiple copies of the same constraint, with different system-generated names, if the data definition language (DDL) statement runs multiple times. Example 5-3 creates tables and their constraints at the same time, using the Example 5-3 Defining Constraints with the CREATE TABLE Statement DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3) CONSTRAINT pk_DeptTab_Deptno PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT u_DeptTab_Dname_Loc UNIQUE (Dname, Loc), CONSTRAINT c_DeptTab_Loc CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); DROP TABLE EmpTab; CREATE TABLE EmpTab ( Empno NUMBER(5) CONSTRAINT pk_EmpTab_Empno PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT r_EmpTab_Mgr REFERENCES EmpTab, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT r_EmpTab_DeptTab REFERENCES DeptTab ON DELETE CASCADE); Example 5-4 creates constraints for existing tables, using the You cannot create a validated constraint on a table if the table contains rows that violate the constraint. Example 5-4 Defining Constraints with the ALTER TABLE Statement -- Create tables without constraints: DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3), Dname VARCHAR2(15), Loc VARCHAR2(15) ); DROP TABLE EmpTab; CREATE TABLE EmpTab ( Empno NUMBER(5), Ename VARCHAR2(15), Job VARCHAR2(10), Mgr NUMBER(5), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) ); --Define constraints with the ALTER TABLE statement: ALTER TABLE DeptTab ADD CONSTRAINT pk_DeptTab_Deptno PRIMARY KEY (Deptno); ALTER TABLE EmpTab ADD CONSTRAINT fk_DeptTab_Deptno FOREIGN KEY (Deptno) REFERENCES DeptTab; ALTER TABLE EmpTab MODIFY (Ename VARCHAR2(15) NOT NULL); Privileges Needed to Define ConstraintsThe creator of a constraint must have the ability to create tables (the Naming ConstraintsAssign names to constraints Choosing your own name makes error messages for constraint violations more understandable, and prevents the creation of duplicate constraints with different names if the SQL statements are run more than once. See the previous examples of the Enabling and Disabling ConstraintsThis section explains the mechanisms and procedures for manually enabling and disabling constraints. enabled constraint. When a constraint is enabled, the corresponding rule is enforced on the data values in the associated columns. The definition of the constraint is stored in the data dictionary. disabled constraint. When a constraint is disabled, the corresponding rule is not enforced. The definition of the constraint is still stored in the data dictionary. An integrity constraint represents an assertion about the data in a database. This assertion is always true when the constraint is enabled. The assertion might not be true when the constraint is disabled, because data that violates the integrity constraint can be in the database. Topics:
Why Disable Constraints?During day-to-day operations, keep constraints enabled. In certain situations, temporarily disabling the constraints of a table makes sense for performance reasons. For example:
Temporarily turning off constraints can speed up these operations. Creating Enabled Constraints (Default)When you define an integrity constraint (using either Example 5-5 Creating Enabled Constraints /* Use CREATE TABLE statement to create enabled constraint (ENABLE keyword is optional): */ DROP TABLE t1; CREATE TABLE t1 (Empno NUMBER(5) PRIMARY KEY ENABLE); /* Create table without constraint and then use ALTER TABLE statement to add enabled constraint (ENABLE keyword is optional): */ DROP TABLE t2; CREATE TABLE t2 (Empno NUMBER(5)); ALTER TABLE t2 ADD PRIMARY KEY (Empno) ENABLE; Include the An Creating Disabled ConstraintsYou define and disable an
integrity constraint (using either Example 5-6 Creating Disabled Constraints /* Use CREATE TABLE statement to create disabled constraint */ DROP TABLE t1; CREATE TABLE t1 (Empno NUMBER(5) PRIMARY KEY DISABLE); /* Create table without constraint and then use ALTER TABLE statement to add disabled constraint */ DROP TABLE t2; CREATE TABLE t2 (Empno NUMBER(5)); ALTER TABLE t2 ADD PRIMARY KEY (Empno) DISABLE; Include the An Enabling Existing ConstraintsAfter you have cleansed the data and filled the empty columns, you can enable constraints that were disabled during data insertion. To enable an existing constraint, use the Example 5-7 Enabling Existing Constraints -- Create table with disabled constraints: DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3) PRIMARY KEY DISABLE, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) DISABLE, CONSTRAINT c_DeptTab_Loc CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) DISABLE ); -- Enable constraints: ALTER TABLE DeptTab ENABLE PRIMARY KEY ENABLE CONSTRAINT uk_DeptTab_Dname_Loc ENABLE CONSTRAINT c_DeptTab_Loc; An Disabling Existing ConstraintsIf you must perform a large insert or update when a table contains data, you can temporarily disable constraints to improve performance of the bulk operation. To disable an existing constraint, use the Example 5-8 Disabling Existing Constraints -- Create table with enabled constraints: DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3) PRIMARY KEY ENABLE, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) ENABLE, CONSTRAINT c_DeptTab_Loc CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) ENABLE ); -- Disable constraints: ALTER TABLE DeptTab DISABLE PRIMARY KEY DISABLE CONSTRAINT uk_DeptTab_Dname_Loc DISABLE CONSTRAINT c_DeptTab_Loc; Guidelines for Enabling and Disabling Key ConstraintsWhen enabling or disabling Fixing Constraint ExceptionsIf a row of a table disobeys an integrity constraint, then this row is in violation of the constraint and is called an exception to the constraint. If any exceptions exist, then the constraint cannot be enabled. The rows that violate the constraint must be updated or deleted before the constraint can be enabled. You can identify exceptions for a specific integrity constraint as you try to enable the constraint. When you try to create or enable a constraint, and the statement fails because integrity constraint exceptions exist, the statement is rolled back. You cannot enable the constraint until all exceptions are either updated or deleted. To determine which rows violate the integrity constraint, include the Modifying ConstraintsStarting with Oracle8i, you can modify an existing constraint with the Example 5-9 Modifying Constraints /* Create & then modify a CHECK constraint: */ DROP TABLE X1Tab; CREATE TABLE X1Tab ( a1 NUMBER CONSTRAINT c_X1Tab_a1 CHECK (a1>3) DEFERRABLE DISABLE ); ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 ENABLE; ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 RELY; ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 INITIALLY DEFERRED; ALTER TABLE X1Tab MODIFY CONSTRAINT c_X1Tab_a1 ENABLE NOVALIDATE; /* Create & then modify a PRIMARY KEY constraint: */ DROP TABLE t1; CREATE TABLE t1 (a1 INT, b1 INT); ALTER TABLE t1 ADD CONSTRAINT pk_t1_a1 PRIMARY KEY(a1) DISABLE; ALTER TABLE t1 MODIFY PRIMARY KEY INITIALLY IMMEDIATE USING INDEX PCTFREE = 30 ENABLE NOVALIDATE; ALTER TABLE t1 MODIFY PRIMARY KEY ENABLE NOVALIDATE;
Renaming ConstraintsOne property of a constraint that you can modify is its name. Situations in which you would rename a constraint include:
Example 5-10 shows how to find the system-generated name of a constraint and change it. Example 5-10 Renaming a Constraint DROP TABLE T; CREATE TABLE T ( C1 NUMBER PRIMARY KEY, C2 NUMBER ); Query: SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T' AND CONSTRAINT_TYPE = 'P'; Result (system-generated name of constraint name varies): CONSTRAINT_NAME
------------------------------
SYS_C0013059
1 row selected.
Rename constraint
from name reported in preceding query to ALTER TABLE T RENAME CONSTRAINT SYS_C0013059 TO T_C1_PK; Query: SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T' AND CONSTRAINT_TYPE = 'P'; Result: CONSTRAINT_NAME ------------------------------ T_C1_PK 1 row selected. Dropping ConstraintsYou can drop a constraint using the
To drop a constraint and all other integrity constraints that depend on it, specify Example 5-11 Dropping Constraints -- Create table with constraints: DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3) PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc), CONSTRAINT c_DeptTab_Loc CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) ); -- Drop constraints: ALTER TABLE DeptTab DROP PRIMARY KEY DROP CONSTRAINT uk_DeptTab_Dname_Loc DROP CONSTRAINT c_DeptTab_Loc; When dropping See Also:
Managing FOREIGN KEY Constraints
Data Types and Names for Foreign Key ColumnsYou must use the same data type for corresponding columns in the dependent and referenced tables. The column names need not match. Limit on Columns in Composite Foreign KeysBecause
foreign keys reference primary and unique keys of the parent table, and Foreign Key References Primary Key by DefaultIf the column list is not included in the Privileges Required to Create FOREIGN KEY ConstraintsTo create a
In both cases, necessary privileges cannot be obtained through a role; they must be explicitly granted to the creator of the constraint. These restrictions allow:
Choosing How Foreign Keys Enforce Referential IntegrityOracle Database allows different types of referential integrity actions to be enforced, as specified with the definition of a
Viewing Information About ConstraintsTo find the names of constraints, what columns they affect, and other information to help you manage them, query the static data dictionary views Example 5-12 Viewing Information About Constraints DROP TABLE DeptTab; CREATE TABLE DeptTab ( Deptno NUMBER(3) PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc), CONSTRAINT c_DeptTab_Loc CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) ); DROP TABLE EmpTab; CREATE TABLE EmpTab ( Empno NUMBER(5) PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT r_EmpTab_Mgr REFERENCES EmpTab ON DELETE CASCADE, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT r_EmpTab_Deptno REFERENCES DeptTab ); -- Format columns (optional): COLUMN CONSTRAINT_NAME FORMAT A20; COLUMN CONSTRAINT_TYPE FORMAT A4 HEADING 'TYPE'; COLUMN TABLE_NAME FORMAT A10; COLUMN R_CONSTRAINT_NAME FORMAT A17; COLUMN SEARCH_CONDITION FORMAT A40; COLUMN COLUMN_NAME FORMAT A12; List accessible constraints in DeptTab and EmpTab: SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB') ORDER BY CONSTRAINT_NAME; Result: CONSTRAINT_NAME TYPE TABLE_NAME R_CONSTRAINT_NAME -------------------- ---- ---------- ----------------- C_DEPTTAB_LOC C DEPTTAB R_EMPTAB_DEPTNO R EMPTAB SYS_C006286 R_EMPTAB_MGR R EMPTAB SYS_C006290 SYS_C006286 P DEPTTAB SYS_C006288 C EMPTAB SYS_C006289 C EMPTAB SYS_C006290 P EMPTAB UK_DEPTTAB_DNAME_LOC U DEPTTAB 8 rows selected. Distinguish between SELECT CONSTRAINT_NAME, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB') AND CONSTRAINT_TYPE = 'C' ORDER BY CONSTRAINT_NAME; Result: CONSTRAINT_NAME SEARCH_CONDITION -------------------- ---------------------------------------- C_DEPTTAB_LOC Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO') SYS_C006288 "ENAME" IS NOT NULL SYS_C006289 "DEPTNO" IS NOT NULL 3 rows selected. For SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB') ORDER BY CONSTRAINT_NAME; Result: CONSTRAINT_NAME TABLE_NAME COLUMN_NAME -------------------- ---------- ------------ C_DEPTTAB_LOC DEPTTAB LOC R_EMPTAB_DEPTNO EMPTAB DEPTNO R_EMPTAB_MGR EMPTAB MGR SYS_C006286 DEPTTAB DEPTNO SYS_C006288 EMPTAB ENAME SYS_C006289 EMPTAB DEPTNO SYS_C006290 EMPTAB EMPNO UK_DEPTTAB_DNAME_LOC DEPTTAB LOC UK_DEPTTAB_DNAME_LOC DEPTTAB DNAME 9 rows selected. Note that:
These constraints are explicitly listed in the
Does foreign key have to match primary key?Since each foreign key value must exactly match the corresponding primary key value, the foreign key must contain the same number and data type of columns as the primary key, and these key columns must be in the same order. A foreign key can also have different column names than the primary key.
Which rule states that every foreign key value must match the primary key value of a record in another table or must be null?Referential integrity requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables.
Can foreign key have null values?A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.
What is the true about foreign key?A foreign key is a column (or combination of columns) in a table whose values must match values of a column in some other table. FOREIGN KEY constraints enforce referential integrity, which essentially says that if column value A refers to column value B, then column value B must exist.
|