4 Replies - 46446 Views - Last Post: 06 April 2010 - 01:54 AM

#1 wartech  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 203
  • Joined: 16-October 06

add not null constraint to existing column SQL /Oracle

Posted 12 April 2009 - 11:38 AM

Hello everyone,
I want to add and name a not null constraint. So far I can add the constraint but don't know how to name it. Your help is appreciated!

ALTER TABLE emp MODIFY (last_name NOT NULL);



Is This A Good Question/Topic? 0
  • +

Replies To: add not null constraint to existing column SQL /Oracle

#2 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: add not null constraint to existing column SQL /Oracle

Posted 13 April 2009 - 06:46 AM

ALTER TABLE emp ADD CONSTRAINT constraint_name NOT NULL (last_name);

Was This Post Helpful? 0
  • +
  • -

#3 wartech  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 203
  • Joined: 16-October 06

Re: add not null constraint to existing column SQL /Oracle

Posted 13 April 2009 - 01:30 PM

Hi Hary,
I am getting the error invalid identifier with that code.
Was This Post Helpful? 0
  • +
  • -

#4 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: add not null constraint to existing column SQL /Oracle

Posted 13 April 2009 - 01:37 PM

Hm, I don't have a active Oracle box to test it atm. On what character that error occurs? If you cannot set a notnull-only contraint, I guess you cannot name a single not null. Strange, As you should be able to create any constraint you want. Maybe you can use a CHECK constraint, but that's harder to perform for the database.
Was This Post Helpful? 0
  • +
  • -

#5 Guest_INDIA_DBA*


Reputation:

Re: add not null constraint to existing column SQL /Oracle

Posted 06 April 2010 - 01:54 AM

View Postwartech, on 13 April 2009 - 12:30 PM, said:

Hi Hary,
I am getting the error invalid identifier with that code.



Hello,
Use this command:
ALTER TABLE TABLE_NAME ADD CONSTRAINTS CHECK CONSTRAINTS_NAME (COL_NAME IS NOT NULL);

Example:

ALTER TABLE TEST_CONS ADD CONSTRAINT CK_TEST_CONS_NAM CHECK(NAM IS NOT NULL);

Example:
CREATE TABLE TEST_CONS

( NAM VARCHAR2(30)

);

This will give custom name to the constraints.

ALTER TABLE TEST_CONS ADD CONSTRAINT CK_TEST_CONS_NAM CHECK(NAM IS NOT NULL);

Then check using sql:
SELECT table_name, constraint_name, search_condition
FROM user_constraints
WHERE constraint_type = 'C' and Constraint_name = 'CK_TEST_CONS_NAM';

Hope this will help you.

Regards
INDIA_DBA
Was This Post Helpful? 0

Page 1 of 1