I'm fairly new in Oracle environment and I'm creating from scratch SQL script for Oracle database, which should be easily ran in any schema or Oracle server. I have some questions regarding to that so I pasted below little sample snippet for two tables (related parent and child) and I would please you if you can give me some tips and suggestions how to create it properly.
-- ...
Create Table Test_Parent
(
Parent_Id Number(4,0) Not Null,
Parent_Name VarChar2(20 Char)
);
Create Table Test_Child
(
Child_Id Number(8, 0) Not Null,
Child_Name VarChar2(20 Char),
Child_Number Number(5,2),
Parent_Parent_Id Number(4,0)
);
-- ...
Alter Table Test_Parent Add Constraint Test_Parent_PK Primary Key(Parent_Id);
Alter Table Test_Parent Add Constraint Test_Parent_Name_UQ Unique(Parent_Name);
Alter Table Test_Child Add Constraint Test_Child_PK Primary Key(Child_Id);
Alter Table Test_Child Add Constraint Test_Child_Number_CK Check(Child_Number > 100);
Create Index Test_Child_Name_IX On Test_Child(Child_Name);
-- Two options to add Reference between two tables. Which one to use?
--Alter Table Test_Child Add Constraint Child_Parent_FK Foreign Key(Parent_Parent_Id) References Test_Parent;
Alter Table Test_Child Add Constraint Child_Parent_FK Foreign Key(Parent_Parent_Id) References Test_Parent(Parent_Id);
-- ...
1. Is it better to add various constraints later below, after all tables were created (like I did above) or is better to add them already in each table definition? I think is easier to create unrelated tables first and create references later, so you don't have to be carefull for table definition order or just run some portion of code which didn't executed correctly.
2. When you create references between two tables is it necesarry to add Parent field name on the right side of keyword References? If you don't add FieldName (above Parent_Id) reference is also created (parent table have only one primary key)? Is this optional for one PK only?
3. Is it necesarry explicitly create indexes for PK fields?
4. If I switch to Modal tab in SQL Developer I can see different icons and symbols in front of table fields (many of them I know). Where I can find legend of all available icons/symbols and their meaning? In SQL Dev manuals I can't find it.
5. What else would you add into this script as necessary, so it will be better and easier to use and maintain database?
6. How do you most often design new database? Do you create script manually or use some GUI tools like SQL Developer and then generate script for whole database? If I do that way I get full of some not necessary code which is hard to maintain and migrate to other database server. Or I'm doing something wrong? What would be correct approach to design new database in your opinion?
Thanks for your all answers, tips and feedback.
BB