* - Beyond the scope of this course, but here for reference purposes
The CASCADE CONSTRAINTS option in Oracle does not drop tables with referential integrity constraints, it only drops the foreign key constraints. We will get into creating tables with foreign keys next week, but for now, here is an example in SQL*Plus:
SQL> CREATE TABLE foo (
id INTEGER NOT NULL,
name VARCHAR(15),
CONSTRAINT pk_foo_id PRIMARY KEY (id)
);
Table created.
SQL> CREATE TABLE bar (
id INTEGER NOT NULL,
name VARCHAR(15),
foo_id INTEGER,
CONSTRAINT pk_bar_id PRIMARY KEY (id),
FOREIGN KEY (foo_id) REFERENCES foo (id)
);
Table created.
SQL> DROP TABLE foo;
DROP TABLE foo
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> DROP TABLE foo CASCADE CONSTRAINTS;
Table dropped.
SQL>
Oracle USED TO treat empty strings the same as NULL, but not since version 9i (version before 10g). The message may have been a legacy message. I have seen conflicting information on this issue, but it would appear that in this case, Oracle equates an empty string to NULL.
SELECT NVL(column1,'abc') FROM table1;
If column1 has NULL values, then this query will return the string 'abc' and not NULL.
I would refer you to this site for a thorough explanation: http://cegt201.bradley.edu/projects/proj2003/equiprd/acid.html
You can. You can even populate it with the data from the other table, too. To do that:
CREATE TABLE NEWTABLENAME AS
SELECT * FROM OLDTABLENAME;
In order to create the new table structure with no new data, add a WHERE clause to the statement which will always be *false*:
CREATE TABLE NEWTABLENAME AS
SELECT * FROM OLDTABLENAME WHERE 1=2;