SQL CHAPTER 4
_ck
Constraint types are designated by abbreviation. What is the abbreviation for CHECK?
_fk
Constraint types are designated by abbreviation. What is the abbreviation for FOREIGN KEY?
_nn
Constraint types are designated by abbreviation. What is the abbreviation for NOT NULL?
_pk
Constraint types are designated by abbreviation. What is the abbreviation for PRIMARY KEY?
_uk
Constraint types are designated by abbreviation. What is the abbreviation for UNIQUE?
separate from
Creating a constraint at column level means the constraint's definition is_____________ the column definition.
included as part of
Creating a constraint at column level means the constraint's definition is___________________________ the column definition.
CONSTRAINT TYPE- Primary Key
Determines which column(s) uniquely identifies each record. The primary key can't be NULL, and the data values must be unique.
CONSTRAINT TYPE - Not Null
Ensures that a specified column can't contain a NULL value. The NOT NULL constraint can be created only with the column-level approach to the table creation.
CONSTRAINT TYPE - Check
Ensures that a specified condition is true before the data value is added to a table. For example, and order's ship date can't be earlier than its order date.
CONSTRAINT TYPE - Unique
Ensures that all data values stored in a specified column are unique. The UNIQUE constraint differs from the PRIMARY KEY constraint in that it allows NULL values
You can attempt a row insert with a duplicate or NULL Customer# value to verify that the primary key rejects the row
How can you test a primary key constraint?
SYS_Cn, where n is assigned a numeric value to make the name unique.
If Oracle 12c names the constraint, what format do they follow?
you must create the constraint at table level.
If the constraint applies to more than one column, _____________________________________________.
after
If you create the constraint at the same time you're creating a table, you list the constraint _______________ all the columns are defined.
CONSTRAINT TYPE - Foreign Key
In a one-to-many or parent-child relationship, the constraint is added to the "many" table. The constraint ensures that if a value is entered in a specified column, it must already exist in the 'one" table, or the record isn't added.
tablename_columnname_constrainttype for example: customers_customer#_pk
What is industry convention in naming constraint types?
Unique allows NULL values, Primary does not.
What is the difference between a PRIMARY KEY CONSTRAINT and a UNIQUE constraint?
1. Name the constraint following the same rules as for tables and columns. 2. Omit the constraint name and allow Oracle 12c to generate the name.
When creating a constraint, you have two options for naming. What are they?
definition
When you create a constraint at the table level, the constraint _________________ is separate from any column definitions.
specified column
When you create constraints at the column level, the constraint applies to the _______________________________.
value
A NULL value means the column contains no value.
NOT NULL
A ________________________ can only be created at column level.
you can't add or delete the entire row if any column value violates a constraint.
Although a constraint can be created at the column level or the table level, the constraint is ALWAYS enforced on a row level, which means that ________________________________________________________.
conlumnname [CONSTRIANT constraintname] constrainttype,
Syntax for creating a column-level constraint.
[CONSRAINT constraintname] constrainttype (columnname, . . .),
Syntax for creating a table-level constraint.
ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY (columnname);
Syntax of the ALTER TABLE command to add a PRIMARY KEY constraint.
PRIMARY KEY FOREIGN KEY UNIQUE CHECK NOT NULL
The constraint type uses the following keywords to identify the type of constraint you're creating:
at the end of the constraint definition inside parentheses, instead of at the beginning of the constraint definition.
The main difference in the syntax of a column level constraint and a table level constraint is that you provide column names for the table level constraint __________________________________________.
you want to give the constraint a specific name instead of letting Oracle 12c generate one for you.
The optional CONSTRAINT keyword is only used if ______________________________________________.
different parts
The two methods of creating a constraint differ only in that they include the constraint code in different parts of the CREATE TABLE statement.
ALTER TABLE orderitems ADD CONSTRAINT orderitems_order#_pk PRIMARY KEY (order#, item#);
To indicate that the primary key for a table consists of more than one column, simply list the column names, separated by commas, in parentheses after the constraint type. Example:
Because a table can have only one PRIMARY KEY constraint.
Why do some developers not include column names when naming a PRIMARY KEY constraint?
So that you can identify it in the future due to the descriptive name.
Why is it better to to provide a descriptive name for a constraint rather than allow Oracle 12c to name it?
Constraint violations display an error message referencing the constraint name. Just by viewing the error message, you know what table, column, and type of constraint are the issue.
Why is using a naming convention helpful in evaluating the cause of a constraint violation?
True
You can add constraints after the table is created by using the ALTER TABLE command.
True
You can add constraints during table creation as part of the CREATE TABLE command.
the constraint is being defined for more than one column (for example, a composite primary key)
You can create any type of constraint at the column level - unless:
Database constraints
__________________________________ are the last line of defense to check data before it's added before its added to the database.
Application data verification methods
__________________________________ serve as the first line of defense to ensure data integrity.