Home » SQL & PL/SQL » SQL & PL/SQL » Constraints (9i)
Constraints [message #400627] Wed, 29 April 2009 02:41 Go to next message
MR[T]
Messages: 62
Registered: March 2009
Member
There are two ways to define a constraint

1)
Create table employees(
emp_id number(6),
last_name varchar2(25) NOT NULL --SYSTEM NAMED
hiredate date constraint emp_hiredate_nn NOT NULL USER NAMED
)


I only want to know that Why we do not use only system Constraints ,i think system Constraints are easy to use instead of User Constraints .
Regards
MR.T
Re: Constraints [message #400634 is a reply to message #400627] Wed, 29 April 2009 03:04 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you have two options, you may use whichever you want. I'll use what I prefer, and someone else will do as he/she wants. Doesn't matter.
Re: Constraints [message #400659 is a reply to message #400627] Wed, 29 April 2009 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer the opposite option.

Regards
Michel
Re: Constraints [message #400702 is a reply to message #400627] Wed, 29 April 2009 05:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
System named constraints work well for NOT NULL columns, where you get an ORA-1400 if you brech the constraint.

For Check constraints, having a constraint names with a table_name as part of the constraint can make tracking down a problem significantly quicker.
Re: Constraints [message #400948 is a reply to message #400627] Thu, 30 April 2009 09:31 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
Thanks Everyone . Smile
Re: Constraints [message #422502 is a reply to message #400627] Wed, 16 September 2009 22:20 Go to previous messageGo to next message
QuickGun
Messages: 8
Registered: September 2009
Location: SG
Junior Member
Hi All,
I have some system named constraints on a group of tables. I would want to drop them as I don't see much use of them.

I am looking to create a dynamic sql to have them dropped.

May i know from which table i get information on this?

Thanks...
Re: Constraints [message #422504 is a reply to message #422502] Wed, 16 September 2009 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Be sure you have valid backup & tested restoration procedure
before dropping anything.

SQL> desc dba_constraints
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE				    VARCHAR2(1)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 SEARCH_CONDITION				    LONG
 R_OWNER					    VARCHAR2(30)
 R_CONSTRAINT_NAME				    VARCHAR2(30)
 DELETE_RULE					    VARCHAR2(9)
 STATUS 					    VARCHAR2(8)
 DEFERRABLE					    VARCHAR2(14)
 DEFERRED					    VARCHAR2(9)
 VALIDATED					    VARCHAR2(13)
 GENERATED					    VARCHAR2(14)
 BAD						    VARCHAR2(3)
 RELY						    VARCHAR2(4)
 LAST_CHANGE					    DATE
 INDEX_OWNER					    VARCHAR2(30)
 INDEX_NAME					    VARCHAR2(30)
 INVALID					    VARCHAR2(7)
 VIEW_RELATED					    VARCHAR2(14)

Re: Constraints [message #422505 is a reply to message #422504] Wed, 16 September 2009 22:41 Go to previous message
QuickGun
Messages: 8
Registered: September 2009
Location: SG
Junior Member
Yup, i am dropping off the constraints for backup tables only. For the live, main tables, i don't drop any.

Thank you for your quick reply...
Previous Topic: Left Outer Joins - query is too slow
Next Topic: Beginner question on Objects
Goto Forum:
  


Current Time: Sat Oct 01 10:59:58 CDT 2016

Total time taken to generate the page: 0.08964 seconds