Chapter 4: Constraints

What is the maximum number of columns you can define as a primary key when using the column-level approach to creating a table? 0 1 30 255

1 If you were using the table level approach, you could define up to 2 columns and have it be the composite primary key. But you can't do that at the column level.

Which of the following types of restrictions can be viewed with the DESCRIBE command? NOT NULL FOREIGN KEY UNIQUE CHECK

NOT NULL

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?

Not NULL

What is the simplest way to determine whether a particular column can contain NULL values?

DESC [table_name];

When must you define constraints at the table level rather than the column level?

If the constraint applies to more than one column (like a composite key), you must create the constraint at the table level.

Which of the following data dictionary objects should be used to view information about the constraints in a database? USER_TABLES USER_RULES USER_COLUMNS USER_CONSTRAINTS None of the above objects should be used

The USER_CONSTRAINTS view, which is part of the data dictionary, is used to identify existing constraints.

How can you verify the constraints that exist for a table?

The USER_CONSTRAINTS view, which is part of the data dictionary, is used to identify existing constraints. Ex: SELECT constraint_name, constraint_type, r_constraint_name FROM user_constraints WHERE table_name = 'EQUIP';

The FOREIGN KEY constraint should be added to which table? the table representing the "one" side of a one-to-many relationship the parent table in a parent-child relationship the child table in a parent-child relationship the table that doesn't have a primary key

the child table in a parent-child relationship

How is adding a NOT NULL constraint to an existing table different from adding other types of constraints?

A NOT NULL constraint can be added only to an existing column by using the ALTER TABLE ... MODIFY command.

What is the difference between a PRIMARY KEY constraint and a UNIQUE constraint?

A UNIQUE constraint allows NULL values, which aren't permitted with a PRIMARY KEY constraint.

Which of the following commands can be used to enable a disabled constraint? ALTER TABLE ... MODIFY ALTER TABLE ... ADD ALTER TABLE ... DISABLE ALTER TABLE ... ENABLE

ALTER TABLE ... ENABLE

Which of the following is a valid SQL statement? CREATE TABLE tablel (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20) PRIMARY KEY, col3 DATE DEFAULT SYSDATE, col4 VARCHAR2(2)); CREATE TABLE tablel (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2 (2) NOT NULL, CONSTRAINT tablel_col3_ck CHECK (col3=SYSDATE)); CREATE TABLE tablel (col1 NUMBER, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2(2), PRIMARY KEY (coll)); CREATE TABLE tablel (coll NUMBER, col2 VARCHAR2(20), col3 DATE DEFAULT SYSDATE, col4 VARCHAR2(2);

CREATE TABLE tablel (col1 NUMBER, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2(2), PRIMARY KEY (col1));

How many NOT NULL constraints can be created at the table level by using the CREATE TABLE command? 0 1 12 30 255

0

What is the maximum number of PRIMARY KEY constraints allowed for a table? 1 2 30 255

1

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? 1 2 3 4

2

Which of the following is the valid syntax for adding a PRIMARY KEY constraint to an existing table? ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (columnname); ALTER TABLE tablename ADD CONSTRAINT (columnname) PRIMARY KEY constraintname; ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY; None of the above is valid syntax.

ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (columnname);

Which of the following SQL statements is invalid and returns an error message? ALTER TABLE books ADD CONSTRAINT books_pubid_uk UNIQUE (pubid); ALTER TABLE books ADD CONSTRAINT books_pubid_pk PRIMARY KEY (pubid); ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid); ALTER TABLE books ADD CONSTRAINT books_pubid_fk FOREIGN KEY (pubid) REFERENCES publisher (pubid); All of the above statements are invalid.

All of the above statements are invalid.

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? CASCADE CASCADE ON DELETE DELETE ON CASCADE DROP ON DELETE CASCADE

CASCADE

Which type of constraint should you use on a column to allow entering only values above 100? PRIMARY KEY UNIQUE CHECK NOT NULL

CHECK

Which type of constraint should you use to ensure that every book has a profit margin between 15% and 25%?

CHECK

Which of the following types of constraints can't be created at the table level? NOT NULL PRIMARY KEY CHECK FOREIGN KEY None of the above constraints can be created at the table level.

NOT NULL

Which of the following commands can you use to rename a constraint? RENAME ALTER CONSTRAINT MOVE NEW NAME None of the above commands can be used.

None of the above commands can be used. If a constraint needs to be changed in any way, you must delete (DROP) it and then create new constraint with the changes that you want.

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? ALTER TABLE ... MODIFY ALTER TABLE ... ADD ALTER TABLE ... DISABLE None of the above commands can be used.

None of the above commands can be used.Should be ALTER TABLE...DROP PRIMARY KEY;

Which of the following is a valid SQL command? ALTER TABLE books ADD CONSTRAINT UNIQUE (pubid); ALTER TABLE books ADD CONSTRAINT PRIMARY KEY (pubid); ALTER TABLE books MODIFY (pubid CONSTRAINT NOT NULL); ALTER TABLE books ADD FOREIGN KEY CONSTRAINT (pubid) REFERENCES publisher (pubid); None of the above commands is valid.

None of the above commands is valid.

Which of the following statements is correct? A PRIMARY KEY constraint allows NULL values in the primary key column(s). You can enable a dropped constraint if you need it in the future. Every table must have at least one PRIMARY KEY constraint, or Oracle 12c doesn't allow the table to be created. None of the above statements is correct.

None of the above statements is correct.

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

A table can have a maximum of how many PRIMARY KEY constraints?

One. If your table has a composite key, you must create the primary key constraint in a CREATE TABLE statement. Ex: ALTER TABLE orderitems ADD CONSTRAINT orderitems_order#item#_pk PRIMARY KEY (order#, item#);

Which of the following is not a valid constraint type? PRIMARY KEYS UNIQUE CHECK FOREIGN KEY

PRIMARY KEYS

In the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entries in the specified columns to be unique, which of the following statements is correct? The constraint can be created only with the ALTER TABLE command. The constraint can be created only with the table-level approach. The constraint can be created only with the column-level approach. The constraint can be created only with the ALTER TABLE ... MODIFY command.

The constraint can be created only with the table-level approach.

What is the difference between disabling a constraint and dropping a constraint?

To DISABLE a constraint, you issue an ALTER TABLE command and change the constraint's status to DISABLE. Later, you can reissue the ALTER TABLE command and change the constraint's status back to ENABLE. If you create a constraint and then decide it's no longer needed (or you find an error in the constraint), you can delete the constraint from the table with the DROP (constraintname) command.