SQL Techniques - data definition language

• SQL syntax:
  • database creation techniques
  • security management
• see also:
  • Delphi Programming Main Index
  • SQL techniques - data manipulation language

SQL Syntax:

Database creation/structure techniques:

• CREATE TABLE statements:
  • SQL Anywhere examples:
    • CREATE TABLE tablename
    • fieldname1 datatype {not null}
    • PRIMARY KEY (fieldname1)
• CREATE INDEX statements:
  • examples:
    • CREATE INDEX index_name ON tablename(fieldname {[ASC | DESC] )
• CREATE UNIQUE INDEX statements:
  • examples:
    • CREATE UNIQUE INDEX index_name ON tablename(fieldname1 {[ASC | DESC]}, fieldname2 {[ASC | DESC]})
• ADD FOREIGN KEY statements:
  • ALTER TABLE tablename
  • ADD FOREIGN KEY fk_name (fieldname)
  • REFERENCES tablename2 (fieldname)
  • ON UPDATE RESTRICT
  • ON DELETE RESTRICT
• CREATE TRIGGER statements:
  • these create a trigger within the table which is automatically ran when an event is activated, the syntax may change with RDBMs format
  • SQL Anywhere format examples:
    • CREATE TRIGGER "triggerName"
    • {BEFORE, AFTER} {INSERT, UPDATE, DELETE, UPDATE OF "fieldname1"}
    • ON tablename??
    • REFERENCING [ OLD AS old_name] [NEW AS new_name]
    • FOR EACH ROW
    • [ WHEN( search_condition ) ]
    • BEGIN
    • trigger_action
    • END

Security management:

• SQL Anywhere examples:
  • add new user:
    • GRANT CONNECT TO new_user IDENTIFIED BY password
  • change user's password:
    • GRANT CONNECT TO new_user IDENTIFIED BY new_password
  • assign privileges:
    • GRANT privilege {fieldname1, fieldname2} ON objectname TO user {WITH GRANT OPTION}
      • where privilege can be: [ALTER,REFERENCES,DELETE,INSERT,UPDATE,SELECT,ALL,EXECUTE]
      • fieldnames are optional where restricting privilege to those columns only
      • WITH GRANT OPTION is optional and grants the user with ability to grant this permission to other users
    • GRANT [DBA, RESOURCE] TO user
      • grants DBA or RESOURCE authority to the user
  • revoke permissions:
    • REVOKE privilege ON objectname FROM user
  • allow a user to have members (ie. become a group):
    • GRANT GROUP TO user_group
  • add users to group:
    • GRANT MEMBERSHIP IN GROUP user_group TO user1