Wednesday, January 21, 2009

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.

No comments:

Post a Comment