Ice Ice - 5 months ago 16
SQL Question

How to add constraint to table in sql?

I create a table

create table CARS{
CAR_ID NUMBER(10), CONSTRAINT X_CAR_ID NOT NULL
}


and now I want to change the name of the constraint, so I drop the constraint:

ALTER TABLE CARS DROP CONSTRAINT X_CAR_ID;


This works correclty but, when I tried to add new constraint I have a problem,
my query:

ALTER TABLE CARS ADD CONSTRAINT XX_CAR_ID (CAR_ID) NOT NULL;


I thought that query, will be working correctly, but I get only error report:

Error report -
SQL Error: ORA-00904:


How to add correctly this constraint ?

jpw jpw
Answer

While I couldn't test it I believe the statement below is what you want:

ALTER TABLE CARS MODIFY CAR_ID CONSTRAINT XX_CAR_ID NOT NULL;

Oracle uses the modify keyword in this context.

To rename it without dropping first you would use:

alter table cars rename constraint x_car_id to xx_car_id;

See the reference for more info.