Re: OFFTOPIC Re: Naming DEFAULT constraints

From: Paul <paulwragg2323_at_hotmail.com>
Date: 29 Jan 2007 00:56:55 -0800
Message-ID: <1170061015.427404.34230@s48g2000cws.googlegroups.com>

Not sure if anybody will reply now but here goes, I hope so!

As stated I cannot rename the constraints once created, I need to be able to name them. Here is what I have done:

SQL> CREATE TABLE TEST1
  2 ( TESTCOL1 NUMBER(9,0) DEFAULT 3 NOT NULL ); Table created.

  1 SELECT CON.CONSTRAINT_NAME
  2 FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL, USER_TAB_COLS COLS
  3 WHERE CON.TABLE_NAME = 'TEST1'

  4   AND COL.COLUMN_NAME = 'TESTCOL1'
  5   AND CON.TABLE_NAME = COL.TABLE_NAME
  6   AND CON.TABLE_NAME = COLS.TABLE_NAME
  7   AND COL.COLUMN_NAME = COLS.COLUMN_NAME
  8   AND CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME

  9 AND COLS.DATA_DEFAULT IS NOT NULL
 10* AND CON.CONSTRAINT_TYPE = 'C'
SQL> / CONSTRAINT_NAME

---

SYS_C0060925 So, the default 'constraint' is created. I know that strictly speaking it is not a bona fida constraint but it is there in the list. So from this I had hoped that I could maybe do the following instead:

  1 CREATE TABLE TEST1
  2* ( TESTCOL1 NUMBER(9,0) NOT NULL) SQL> / Table created.

I can then do this:

  1 ALTER TABLE TEST1
  2 MODIFY
  3* (TESTCOL1 NUMBER(9,0) DEFAULT 3)
SQL> / Table altered.

But I want to be able to associate a name with this default 'constraint'.

I can rename it, so if we go back to the example above I can now do:

SQL> ALTER TABLE TEST1
  2 RENAME CONSTRAINT SYS_C0060927
  3 TO MyNamedConstraint;

Table altered.

SQL> SELECT CON.CONSTRAINT_NAME
  2 FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL, USER_TAB_COLS COLS
  3 WHERE CON.TABLE_NAME = 'TEST1'

  4    AND COL.COLUMN_NAME = 'TESTCOL1'
  5    AND CON.TABLE_NAME = COL.TABLE_NAME
  6    AND CON.TABLE_NAME = COLS.TABLE_NAME
  7    AND COL.COLUMN_NAME = COLS.COLUMN_NAME
  8    AND CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME

  9 AND COLS.DATA_DEFAULT IS NOT NULL  10 AND CON.CONSTRAINT_TYPE = 'C'; CONSTRAINT_NAME

---

MYNAMEDCONSTRAINT So I now end up with what I wanted, a DEFAULT constraint with a controlled name rather than a system generated one. This is what I was trying to acheive, but I want to acheive it by being able to name it in the first place, rather than having to create it, find out the system generated name, then rename it. So if anybody knows of a way then please let me know. I hope this is clearer now, apologies for now providing specific examples before, to be honest I assumed there would be a quick simple answer but clearly not!

Oracle 10.2.0.1.0

Thanks,

Paul Received on Mon Jan 29 2007 - 02:56:55 CST