Constraints
When a foreign key constraint exists between two tables, by default, a record can't deleted from the parent table if matching entries exist in the child table.
-> delete data from the cild table, then delete from the parent table alternative to this: use keywords "ON DELETE CASCADE" ex> ALTER TABLE orders ADD constraint orders_customer#_fk foreign key (customer#) REFERENCE customers (customer#) ON DELETE CASCADE;
How many NOT NULL constraints can be created at the table level by using the CREATE TABLE command?
0
What is the maximum number of PRIMARY KEY constraints allowed for a tble?
1
What is the maximum number of columns you can define as a primary key when using the column-level approach to creating a table?
1
conditions to use for CHECK CONSTRAINT
1. < (less than) 2. > (greater than) 3. BETWEEN (range) ex> retail BETWEEN 0 AND 200 4. IN (list of values) ex> region IN ('NE', 'SE', 'NW', 'SW')
Guideline to follow when applying constraints
1. A NOT NULL constraint shouldn't be assigned to a PRIMARY KEY column. PRIMARY KEY enforces both NOT NULL and UNIQUE constraints 2. CHECK, FOREIGN KEY, and UNIQUE KEY constraints don't require a value. A NOT NULL constraint myst be used along with these constraints to require input for a column 3. If a DEFAULT option is set for a column, a NOT NULL constraint shouldn't be used. If no value is provided for the column, the DEFAULT value is assigned.
Two options to allow deleting the parent table
1. DROP the child table and then DROP the parent table 2. DROP the parent table with the CASCADE CONSTRAINTS option. (deletes the FOREIGN KEY constraint in the child table and then deletes the parent table) ex> DROP TABLE customers CASCADE CONSTRAINTS;
5 types of constraints
1. PRIMARY KEY 2. FOREIGN KEY 3. UNIQUE 4. CHECK 5. NOT NULL
Syntax for PRIMARY KEY creating
1. created during table creation: CREATE TABLE newtable (firstcol NUMBER PRIMARY KEY, secondcol VARCHAR2(20); OR CREATE TABLE newtable (firstcol NUMBER, secondcol VARCHAR2(20), CONSTRAINT constraintname_pk, PRIMARY KEY (firstcol)); 2. created after table is created ALTER TABLE newtable ADD CONSTRAINT constraintname_pk PRIMARY KEY (firstcol));
TO add constraints
1. during table creation as part of CREATIVE TABLE command 2. after the table is created by using ALTER TABLE command a. create at column level? b. create at table level?
You're creating a new table consisting of three columns: Col1, Col2, and Col3. Col1 should be the primary key and can't have any NULL values, and each entry should be unique. Col3 must not contain any NULL values either. How many total constraints do you have to create?
2
Which of the following SQL statements is invalid and returns an error message?
ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid);
Add constraint during creating a table
CREATE TABLE dept (deptid NUMBER(2), dname VARCHAR2(20) NOT NULL, fax VARCHAR2(12), CONSTRAINT dept_deptid_pk PRIMARY (deptid), CONSTRAINT dept_dname_uk UNIQUE (dname));
syntax for UNIQUE constraint
CREATE TABLE newtable (firstcol NUMBER, secondcol VARCHAR2(20) UNIQUE); OR CREATE TABLE newtable (firstcol NUMBER, secondcol VARCHAR2(20), CONSTRAINT constraintname_uk UNIQUE (secondcol)); ALTER TABLE tablename ADD CONTRAINT constraintname_uk UNIQUE (secondcol);
Which of the following types of constraints is used to enforce referential integrity?
FOREIGN KEY
Which of the following types of constraints can't be created at the table level?
NOT NULL
Which of the following types of restrictions can be viewed with the DESCRIBE command?
NOT NULL
Which of the following is a vlid SQL command? a. ALTER TABLE books ADD CONSTRAINT UNIQUE (pubid); b. ALTER TABLE books ADD CONSTRAINT PRIMARY KEY (pubid); c. ALTER TABLE books MODIFY (pubid CONSTRAINT NOT NULL); d. ALTER TABLE books ADD FOREIGN KEY CONSTRAINT (pubid) REFERENCES publisher (pubid); e. None of the above commands is valid.
None of the above is valid
Which of the following keywords allows the user to delete a record from a table, even if rows in another table reference the record through a FOREIGN KEY constraint?
ON DELETE CASCADE
Which of the following keywords must have been included during the creation of a FOREIGN KEY constraint to allow a row from the parent table to be deleted, even if it is referenced by a row in the child table?
ON DELETE CASCADE
To which table do you add a FOREIGN KEY constraint if you want to make certain every book ordered exists in the Books table?
Orderitems (childtable)
A FOREIGN KEY constraint can only reference a column in another table that has been assigned a(n) _________ constraint.
PRIMARY
constraint type abbreviation
PRIMARY KEY: _pk FOREIGN KEY: _fk UNIQUE: _uk CHECK: _ck NOT NULL: _nn
Which of the following is not a valid constraint type?
PRIMARY KEYS
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) _______ for a FOREIGN KEY constraint.
R
constraints
Rules used to enforce business rules, practices, and policies to ensure the accuracy and integrity of data Constraints do not allow data to be added to tables if the data violates certain rules.
UIQUE CONSTRAINT
To ensure that two records can't have the same value stored in the same column. difference with PRIMARY KEY : UNOQUE constraint allows NULL values ALTER TABLE tablename ADD [CONSTRAINT constraintname] UNIQUE (columnname); ex> ALTER TABLE books ADD CONSTRAINT books_title_uk UNIQUE (title);
CHECK CONSTRAINT
To prevent data entry errors by specifying condition to be met before a record is added to a table. ALTER TABLE tablename ADD [CONSTRAINT constraintname] CHECK (condition); ex> ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);
A FOREIGN KEY constraint can't reference a column in a table that has not been designated as the primary key for the referenced table.
Ture
The purpose of the _______ constraint is to ensure that two records do not have the same value stored in the same column. However, it can contain NULL values.
UNIQUE
What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?
UNIQUE constraint allows null value
Which of the following data dictionary objects should be used to view information about the constraints in a database?
USER_CONSTRAINTS
To view constraint information
USER_CONSTRAINTS view -> part of data dictionary SELECT constraint_name, constraint_type, search_condition, r_constraint_name FROM user_constraints WHERE table_name = "EQUIP'; P for Primary key constraint C for Check key constraint, NOT NULL constraint U for Unique R for Foreign key constraint
NOT NULL CONSTRAINT
a special CHECK. constraint with the condition IS NOT NULL prevents users from adding a row that contains a NULL value in the specified column. can be added only to an existing column by using ALTER TABLE...MODIFY command ALTER TABLE tablename MODIFY (columnname [CONSTRAINT constraintname] NOT NULL); EX> ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);
What is the simplest way to determine whether a particular column can contain NULL values?
add a null value and run it
When must you define constraints at the table level rather than the column level?
all constraints except NOT NULl constraint
How can constraints be added to a table?
as part of the CREATE TABLE command as part of the ALTER TABLE command
How is adding a NOT NULL constraint to an existing table different from adding other types of constraints?
cannot applied at Table level should apply at column level
Which type of constraint should you use to ensure that every book has a profit margin between 15% and 25%?
check constraint
A PRIMARY KEY that consists of more than one column is called a(n) ____________ key.
composite
Create constraints at column level
constraint applied to the specified column. If constraint applies to more than one column, it SHOULD be created at TABLE LEVEL columnname [CONSTRAINT constraintname] constrainttype, NOT NULL constraint can be created ONLY AT THE COLUMN LEVEL
What is the difference between disabling a constraint and dropping a constraint?
disabling does not execute the constraint, but dropping deletes it from the table
Which of the following commands can you use to rename a constraint? a. RENAME b. ALTER CONSTRAINT c. MOVE d. NEW NAME e. None of the above commands can be used. C
e
Which of the following is the standard abbreviation for the constraint FOREIGN KEY?
fk
Create constraints at the table level
list constraint AFTER all the columns are define. [CONSTRAINT constraintname] constrainttype (columnname, ...),
A table can have a maximum of how many PRIMARY KEY constraints?
one
Application data verification methods
serve as the first line of defense to ensure data integrity
If a constraint applies to more than one column, the constraint must be created at the __________ level.
table
Naming constraints
tablename_columnname_constrainttype ex> customers_customer#_pk
Database constraints
the last line of defense to check data before it's added to the database.
If ON DELETE CASCADE. gives an error,
try ALTER TABLE orders DROP CONSTRAINT orders_customer#_fk;
How can you verify the constraints that exist for a table?
use user_constraint
PRIMARY KEY CONSTRAINT
used to enforce the primary key requirements for a table. ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY (columnname); ex> ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY (customer#);
If the ___________ keyword is included when a constraint is created, a constraint name must be provided by the user.
CONSTRAINT
Which type of constraint can be used to make certain the category for a book is included when a new book is added to inventory?
CHECK constraint
FOREIGN KEY CONSTRAINT
ALTER TABLE tablename ADD [CONSTRAINT constraintname] FOREIGN KEY (columnname) REFERENCES referencedtablename (referencedcolumnname); ex> ALTER TBALE orders ADD CONSTRAINT order_customer#_fk FOREIGN KEY (customer#) REFERENCES customers (customer#);
To disable a constraint
ALTER TABLE tablename DISABLE CONSTRAINT constraintname; you can change it to "ENABLE" ALTER TABLE tablename ENABLE CONSTRAINT constraintname;
To delete constraint from a table
ALTER TABLE tablename DROP PRIMARY KEY | UNIQUE (columnname) | CONSTRAINT constraintname;
Which of the following is the valid syntax for adding a PRIMARY KEY constraint to an existing table?
ALTER TABLE.... ADD CONSTRAINT constraintname PRIMARY KEY (column name);
Which of the following commands can be used to enable a disabled constraint?
ALTER TABLE...ENABLE
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE. column will be a(n) _______ for a CHECK constraint.
C
The _________ constraint requires that a specific condition be met before a record can be added to a table.
CHECK
Which type of constraint should you use on a column to allow entering only values above 100?
CHECK
syntax for FOREIGN KEY creation
CREATE TABLE tablename (firstcol NUMBER, second VARCHAR2(20) REFERENCES anothertable (col1)); or CREAT TABLE tablename (firstcol NUMBER, second VARCHAR2(20), CONSTRAINT constraintname_fk FOREIGN KEY (secondcol) REFERENCES anothertable (col1)); ALTER TABLE tablename ADD CONSTRAINT constraintname_fk FOREIGN KEY (secondcol));
syntax for CHECK constraint
CREATE TABLE tablename (firstcol NUMBER, second VARCHAR2(20), thirdcol NUMBER CHECK (BETWEEN 20 AND 30)); OR CREATE TABLE tablename (firstcol NUMBER, second VARCHAR2(20), thirdcol Number, CONSTRIANT constraintname_ck CHECK (thirdcol BETWEEN 20 AND 30)); ALTER TABLE tablename ADD CONSTRAINT constraintname_ck CHECK (thirdcol BEWTEEN 20 AND 30);
syntax for NOT NULL
CREATE TABLE tablename (firstcol NUMBER, second VARCHAR2(20), thirdcol NUMBER NOT NULL); ALTER TABLE tablename MODIFY (thirdcol NOT NULL);
Which command can be used to determine whether or not a column is allowed to contain a NULL value?
DESCRIBE
Which clause will allow you to delete a constraint?
DROP
What is the syntax for the default constraint name provided by the Oracle server?
SYS_Cn
Suppose you created a PRIMARY KEY constraint at the same time you created a table and later decide to name the constraint. Which of the following commands can you use to change the constraint's name?
Should be ALTER TABLE...DROP
The FOREIGN KEY constraint should be added to which table?
The child table in a parent-child relationship
In the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entires in the specified columns to be unique, which of the following statements is correct?
The constraint can be created only with the table-level approach
