DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: create table

  1. #1
    Join Date
    Oct 2000
    Posts
    16
    this is what i have:

    CREATE TABLE EMP (
    EMPNO NUMBER(4) NOT NULL,
    EMPNAME VARCHAR2(25));

    To insert into this table, I have to put in the primary key. I can use a sequence.
    Insert into emp(empno, empname)
    values(emp_seq.nextval, 'aruna');

    In SQL SERver we have the @@IDENTITY key. Also, when we have a column as a primary key, we do not need to specifically insert it into the table.
    Thus, if the above were in SQL Server, i would say:
    Insert into EMP(empname)
    values('aruna')

    How can I in oracle, setup my primary key column such that it enters a unique value by default similar to in SQL Server.

    I tried this:

    create table emp(
    empno number(4) DEFAULT emp_seq.nextval NOT NULL,
    empname varchar2(25));

    I get an error saying something like column not allowed.

    Any ideas, folks?

    Thanks,
    Aruna

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Create Before insert Trigger on emp to insert nextval into empno, e.g.:

    create or replace trigger emp_trigger before insert on emp
    for each row
    declare
    new_empno number;
    begin
    select emp_seq.nextval
    into new_empno
    from dual;
    :new.empno:=new_empno;
    end;

  3. #3
    Join Date
    Oct 2000
    Posts
    16

    Akkerend

    I couldn't get it to work. Is there a trick to writing theinsert statement?

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    If you have table and trigger, then
    'Insert into EMP(empname)
    values('aruna');'
    should do the trick.

    What is the problem?



  5. #5
    Join Date
    Oct 2000
    Posts
    16

    akkerend: it worked! Thank you

    Hi! sorry. I forgot to create the sequence. Its working now.
    Thanks a bunch.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width