{"id":955,"date":"2015-09-02T07:33:29","date_gmt":"2015-09-02T07:33:29","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=955"},"modified":"2016-03-01T06:17:41","modified_gmt":"2016-03-01T06:17:41","slug":"oracle-table-creation-constraints-synonyms-grant","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/oracle-table-creation-constraints-synonyms-grant\/","title":{"rendered":"Oracle Table Creation, Constraints, Synonyms, Grant"},"content":{"rendered":"

Whenever a table is created in Oracle its shld be done by Four step process<\/p>\n

    \n
  1. Table Creation<\/li>\n
  2. Adding Constraints(Primary Key, Foreign Key)<\/li>\n
  3. Adding Synonyms<\/li>\n
  4. Giving Grants<\/li>\n
  5. Alter Queries<\/li>\n<\/ol>\n

    Table Creation<\/strong> <\/p>\n

    \r\ncreate table TABLE_NAME_1\r\n( COLUMN_NAME_1   varchar2(50),\r\n  COLUMN_NAME_2   NUMBER(1),\r\n  COLUMN_NAME_3   DATE not null,\r\n  COLUMN_NAME_4   NUMBER(38)\r\n );\r\n<\/pre>\n
    Constraint<\/strong>
    \nQuery 1<\/em><\/p>\n
    \r\nalter table TABLE_NAME_2 \r\n  add constraint FK_COLUMN_NAME_1 foreign key (COLUMN_NAME_1) references TABLE_NAME_1 (COLUMN_NAME_1);\r\n<\/pre>\n
    Query 2<\/em><\/p>\n
    \r\nalter table TABLE_NAME_3 add constraint fk_column_1 \r\nforeign key (COLUMN_1, COLUMN_2) references TABLE_NAME_2 (COLUMN_1, COLUMN_2);\r\n<\/pre>\n
    Synonym<\/strong> <\/p>\n
    \r\n CREATE PUBLIC SYNONYM TABLE_NAME_1 FOR OWNER_NAME.TABLE_NAME_1;\r\n CREATE PUBLIC SYNONYM SEQUENCE_NAME_SEQ FOR OWNER_NAME.SEQUENCE_NAME_SEQ;\r\n GRANT ALL ON SEQUENCE_NAME_SEQ TO ADMIN,PART,ALL_DEVELOPERS;\r\n<\/pre>\n
    Grants<\/strong> <\/p>\n
    \r\n GRANT ALL ON TABLE_NAME_1 TO ADMIN,PART,ALL_DEVELOPERS;\r\n GRANT INSERT,UPDATE,DELETE,SELECT ON TABLE_NAME_1 TO ADMIN,PART,ALL_DEVELOPERS;\r\n<\/pre>\n
    Other Queries<\/strong><\/p>\n
    \r\n ALTER TABLE TABLE_NAME_1 COLUMN_NAME_1 NOT NULL;\r\n ALTER TABLE TABLE_NAME_1 MODIFY COLUMN_NAME_1 varchar2(50);\r\n ALTER TABLE TABLE_NAME_1 DROP COLUMN COLUMN_NAME_1;\r\n ALTER TABLE TABLE_NAME_1 DROP CONSTRAINT COLUMN_NAME_1;\r\n<\/pre>\n
    Things to Consider<\/strong><\/p>\n
    While creating Primary key(No Null Values allowed like unique key) as a combination of two or more columns you should take in to consideration only the non null columns as part of unique key.<\/p>\n
    \r\n ALTER TABLE TABLE_NAME\r\n   ADD CONSTRAINT PK_TABLE_NAME PRIMARY KEY (COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3)\r\n  USING INDEX TABLESPACE INDX;\r\n<\/pre>\n
    COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3 should be a Non Null<\/strong> column.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Whenever a table is created in Oracle its shld be done by Four step process Table Creation Adding Constraints(Primary Key, Foreign Key) Adding Synonyms Giving Grants Alter Queries Table Creation create table TABLE_NAME_1 ( COLUMN_NAME_1 varchar2(50), COLUMN_NAME_2 NUMBER(1), COLUMN_NAME_3 DATE not null, COLUMN_NAME_4 NUMBER(38) ); Constraint Query 1 alter table TABLE_NAME_2 add constraint FK_COLUMN_NAME_1 foreign… Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[165],"tags":[],"class_list":["post-955","post","type-post","status-publish","format-standard","hentry","category-oracle-notes"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/955","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=955"}],"version-history":[{"count":11,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/955\/revisions"}],"predecessor-version":[{"id":1152,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/955\/revisions\/1152"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}