Home » SQL & PL/SQL » SQL & PL/SQL » Doubt regarding trigger and other database objects
Doubt regarding trigger and other database objects [message #233941] Sat, 28 April 2007 06:53 Go to next message
rashmi.uikey
Messages: 2
Registered: April 2007
Junior Member
Hi,

I have a doubt,when i create a trigger same as that of other database object name which is already present in the database,system allows to do so,but if i try to the same thing with some other database object like package,view,table,etc...its gives error : name already exists.

Is there any particular reason for the same?

My another doubt is,if given an option which one should i go for it...primary key or (notnull+ unique) constraint combination? bcoz both works in the same manner except ,i can have only one primary key in a table whereas (notnull+ unique) combination can be many.

[Updated on: Sat, 28 April 2007 06:56]

Report message to a moderator

Re: Doubt regarding trigger and other database objects [message #233953 is a reply to message #233941] Sat, 28 April 2007 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is namespace. You can have object with same name in different namespace but only different names in the same namespace.

2/ Primary key has physical AND logical (functional) meaning. If your design says this(these) column(s) is(are) the primary key then use primary key. If design says this(these) column(s) is(are) are unique the use unique key, if more it says this(these) column(s) is(are) not null then use not null constraint.
Btw, optimizer is aware of these different meanings and may behave differently.

Regards
Michel
Re: Doubt regarding trigger and other database objects [message #234082 is a reply to message #233941] Sun, 29 April 2007 13:27 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi,

1)
In addition to what is told by Michel about namespace..

Quote:
Tables share a namespace with views, sequences, private synonyms, procedures, functions,
packages, materialized views, and user-defined types. Objects sharing a namespace cannot have
the same name.


Means, triggers are not stored in the same tablespace as the table on which it's created. That's why you can use the same name for the trigger as the table name but not for the other object..

2)
Have the concept of these constraint clear, so that you will get the proper idea when to apply which..
Primary key : It is generally used to uniquely identify the record.. So the field which is used for this shuold be declared as primary key ( You can also specify the combination of fields as the primary key)
Unique key : Ensures that the declared field will not have the duplicated value ( however, it can have the null, which is not the case in primary key )
Not Null : When you want to put the restriction that the filed must have any value i.e. must not have null value, you can apply this contraint..

Hope this helps you..

Regards..


Re: Doubt regarding trigger and other database objects [message #234094 is a reply to message #234082] Mon, 30 April 2007 00:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vithalani_dipali wrote on Sun, 29 April 2007 20:27

Means, triggers are not stored in the same tablespace as the table on which it's created. That's why you can use the same name for the trigger as the table name but not for the other object..


NAMEspace, not tablespace.
This has nothing to do with tablespace.
Re: Doubt regarding trigger and other database objects [message #234167 is a reply to message #234094] Mon, 30 April 2007 04:51 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Thank frank to point out that thing , it was by mistake..
Previous Topic: Needed query
Next Topic: Multiple values in CASE statement
Goto Forum:
  


Current Time: Sun Dec 04 19:04:04 CST 2016

Total time taken to generate the page: 0.07680 seconds