- Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć2. - After you create your tables, you may need to change the table structures because you omitted a column, your column definition needs to be changed, or you want to enable or disable constraints. - This lesson will demonstrate how you can amend table structures as well as add and remove constraints.. - D Drop a table.. - D Remove all rows leaving the table definition intact.. - D Add comments to objects and view comments from the data dictionary.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć4. - Once you have created your tables, you can modify their structure by using the ALTER TABLE command. - Add columns, modify the column length, add or drop constraints, and enable or disable constraints by using this command.. - If you want to remove a table, both the rows and the data structure of a table, invoke the DROP TABLE command. - D COMMENT, to add a comment about a database object to the data dictionary.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć6. - You can add columns to a table by using the ALTER TABLE command with the ADD clause.. - ALTER TABLE table. - where: table is the name of the table.. - column is the name of the new column.. - datatype is the datatype and length of the new column.. - NOT NULL adds a NOT NULL constraint to the new column.. - D You can add or modify columns, but you cannot drop them from a table.. - D You cannot specify where the column is to appear. - If a table already contains rows when a column is added, then all of the fields in the new column are initially NULL.. - You can define a NOT NULL column only if the table contains no rows because data cannot be specified for existing rows at the same time that the column is added.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć8. - You can modify a column definition by using the ALTER TABLE command with the MODIFY clause. - Column modification can include changes to a column’s datatype, size, default value, and NOT NULL column constraint.. - column is the name of the column.. - datatype is the datatype and length of the column.. - D Decrease the width of a column if the column contains only null values or if the table has no rows.. - D Change the datatype if the column contains null values.. - D Convert a CHAR column to the VARCHAR2 datatype or convert a VARCHAR2 column to the CHAR datatype if the column contains null values or if you do not change the size.. - D A change to the default value of a column only affects subsequent insertions to the table.. - D Add a NOT NULL constraint only if there are no null values in the column.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć10. - You can add or drop constraints for existing tables by using the ALTER TABLE command with the ADD or DROP clause.. - constraint is the name of the constraint.. - type is the constraint type.. - column is the name of the column affected by the constraint.. - D You can add, drop, enable, or disable a constraint, but you cannot modify its structure.. - D You can add a NOT NULL constraint to an existing column by using the MODIFY clause of the ALTER TABLE command.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć12. - To drop a constraint, you can identify the constraint name from the. - USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views.. - Then, use the ALTER TABLE command with the DROP clause. - The CASCADE option of the DROP clause causes any dependent constraints also to be dropped.. - When you drop an integrity constraint, that constraint is no longer enforced by the Oracle7 Server and is no longer available in the data dictionary.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć14. - You can enable or disable constraints without dropping them or recreating them by using the ALTER TABLE command with the ENABLE or DISABLE clause.. - D If you enable a constraint, that constraint applies to all the data in the table. - All the data in the table must fit the constraint.. - D You can use the ENABLE and DISABLE clauses in both the CREATE TABLE command and the ALTER TABLE command.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć16. - The DROP TABLE command removes the definition of an Oracle7 table. - When you drop a table, the database loses all the data in the table and all the indexes associated with it. - DROP TABLE table [CASCADE CONSTRAINTS];. - D All data is deleted from the table.. - D Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.. - The Oracle7 Server does not question the action when you issue the DROP TABLE command. - If you own that table or have a high level privilege, then the table is immediately removed. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć18. - You must be the owner of the object you rename.. - You must be the owner of the table or have DELETE TABLE system privileges to truncate a table.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć20. - You can add a comment of up to 2000 bytes about a column, table, view, or snapshot by using the COMMENT command. - The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:. - COMMENT ON TABLE table | COLUMN table.column IS ’text’;. - column is the name of the column in a table.. - text is the text of the comment.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć22. - D You can create a table and the indicated constraints.. - D Create a table based on another table by using a subquery.. - ALTER TABLE. - DROP TABLE. - D Remove all rows from a table and release the storage space used by the table.. - D Query the data dictionary to view the comment.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć24. - In this practice, you will create, modify, and drop tables related to the EMPLOYEE and DEPARTMENT tables using the commands covered in this lesson.. - D Creating a new table by using the CREATE TABLE AS syntax. - D Displaying information in data dictionary views. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć26. - Create a WORKER table, which copies the data from the EMPLOYEE table.. - Describe the table to confirm its structure.. - Note the types and names of the constraints.. - Compare these constraints to those in the EMPLOYEE table. - Add a table level PRIMARY KEY constraint to the WORKER table using the ID column. - Add a foreign key reference from the DEPARTMENT table to the DEPT_ID column in the WORKER table. - Display the object names and types from the USER_OBJECTS data dictionary view. - Drop the EMPLOYEE table, while leaving the WORKER table in the database.. - Add a comment to the WORKER and DEPARTMENT table definitions describing the tables. - Confirm your additions in the data dictionary.. - Introduction to Oracle: SQL and PL/SQL Using Procedure Builder 12Ć28
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt