First, create a tabular format:Create Tabletable name (Whether the Field field type can be empty, ...); Comment on columnTable/Field isAn example of a comment:Create TableCompany (com_idINTEGER not NULL, Com_nameVARCHAR2( +)NULL, Com_addressVARCHAR2( -)NULL); Second, create a primary key/FOREIGN Key1, create a PRIMARY key constraint without naming the tableCreate Tabletest1 (IDint Primary Key not NULL, namevarchar2( -))----------------------have namedCreate Tabletest2 (IDint, namevarchar2( -),constraintixd_idPrimary Key(ID));----------------------SelectUser_ind_columns.index_name,--Query Index nameUser_ind_columns.column_name,--Query index field nameUser_ind_columns.column_position,--The location of a column or property in an indexUser_indexes.uniqueness--Constraints fromUser_ind_columns, User_indexes--Save index name, table/system view corresponding to table and column table with index namewhereUser_ind_columns.index_name=User_indexes.index_name andUser_ind_columns.table_name= 'test2';2, the ALTER statement creates a primary keyAlter TableCompanyAdd constraintPk_ CompanyPrimary Key(com_id);3, modify PRIMARY KEY constraint disabled/Enable primary keyAlter TableJack DisablePrimary Key;--DisabledAlter TableJack EnablePrimary Key;--enabled------------------------------------------------Renaming a primary keyAlter TableJack Renameconstraintpk_id topk_jack_id;4, delete the primary key in the table without naming first using the User_cons_columns table to check the winner Key name:SelectOwner,constraint_name,table_name,column_name fromUser_cons_columnswheretable_name= 'JACK';SelectTable_name,index_name fromUser_indexeswheretable_name='JACK';Alter TableJackDrop constraintsys_c0011105;--------------------------------------------have namedSelectOwner,constraint_name,table_name,column_name fromUser_cons_columnswheretable_name= 'JACK';Alter TableJackDrop constraintixd_id;5To Add a foreign KEY constraint to the tableAlter TableTest1Add constraintFk_nameForeign Key(test1_out_id)Referencestest2 (test2_id)6, remove the foreign KEY constraintAlter TableTest1Drop constrainttest1_out_id; sequence Operation1, create a sequenceCreateSequence Emp_sequence--Sequence nameIncrement by 1 --add a few each timeStart with 1 --counting starting from 1, initial valueNomaxvalue/MaxValue N/MinValue N--max/MAX/min not setNocycle/Cycle--keep accumulating, not looping/loopingCacheTen/NoCache--cache Several values, default2, modify the sequenceAlterSequence sequence name[increment by N][{maxvalue/minvalue N|nomaxvalue}][{cycle|nocycle}][{Cache N|nocache}]; Cannot modify the initial value of a sequence3, delete the sequenceDropsequence sequence name; iv. Other OperationsAlter TableTest Rename toTest1--Modify Table nameAlter TableTest RenamecolumnName toName1--Modify table column namesAlter TableTest Modify Name1 Number( -)--modifying field TypesAlter TableTestAddAddressvarchar2( +)--Adding table ColumnsAlter TableTestDrop columnName--Delete a table columnPractice:--Add FieldAlter TableCompanyAddPhonevarchar2( A);--modifying field TypesAlter TableCompany Modify Phone Number( A);--Modify Field namesAlter TableCompany RenamecolumnPhone toAAA;--Delete a fieldAlter TableCompanyDrop columnaaa
Oracle learns 04-Table operations (primary key, foreign key, sequence)