Creating Constraints When Creating a Table

Have you ever created a table with 'Create ... as Select ...', and that was so easy and automatic, you wondered why all the constraints didn't just pop into place? Well, after coding for a bit, I realized it was quite a bit more complicated than I initially thought. In fact, the query got long enough that I stored it as an attachment: Create_Constraints.SQL.

As shown in the screen print, you will have to answer several questions for the query to have enough information to generate the code. When done, an output file is produced that you run (or edit first, then run).

Questions in Yellow:
1. Enter the schema containing the original table (or press enter if you wish to use the current schema).
2. Enter the table name that we're copying from
3. Enter the schema the new table will be in (or enter if the same as the original)
4. Enter the new table name following table naming rules as defined by Oracle.

Questions in Orange:
You're shown a list of the current constraint names, and their length. If this table will be in the same schema, these will need to be changed.
5. Try to pick a pattern the query can use as a change factor (i.e., 'EMP_').
6. Keep in mind the length of the new pattern you supply.

What's shown in green is the actual output. It is also spooled to CrTbl.SQL, and can be edited there.

Please send me an email if you use this code, and have any problems with it.

Check for Alpha Only

This is a way to restrict data entry to alpha if you are using a release below 10G. It's not particularly pretty, but it works.

I'm basically translating all alpha characters to spaces, and then trimming them out,which of course, leaves everything else. Then I check for a length. Remove the UPPER if you don't want lower case.

Create Table Testalpha
    (Alphaonly  Varchar2(10)
                Constraint Ck_Alphaonly Check
                    (Length
                          (Trim
                              (Translate
                                       (Upper(alphaonly),
                                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                                       ' '
                                        )
                               )
                         ) is null
                     )
     );

In 10G, you can use the REGEXP functions:

Create Table Testalpha
    (Alphaonly  Varchar2(10)
                Constraint Ck_Alphaonly Check
                    (Regexp_Like(AlphaOnly, '^[[:alpha:]]+$')
                     )
     );

Global Temporary Table

CREATE GLOBAL TEMPORARY TABLE global_temp_table1
 ON COMMIT PRESERVE ROWS
 AS
--------------------------------------------------
Select field,
;
TRUNCATE TABLE GLOBAL_TEMP_Table1;
  DROP TABLE GLOBAL_TEMP_Table1;