How to Alter Constraint

How to Alter Constraint

You can not alter constraints ever but you can drop them and then recreate.

Have look on this

ALTER TABLE your_table DROP CONSTRAINT ACTIVEPROG_FKEY1;

and then recreate it with ON DELETE CASCADE like this

ALTER TABLE your_table
add CONSTRAINT ACTIVEPROG_FKEY1 FOREIGN KEY(ActiveProgCode) REFERENCES PROGRAM(ActiveProgCode)
    ON DELETE CASCADE;

hope this help

how to modify an existing check constraint?

You have to drop it and recreate it, but you don't have to incur the cost of revalidating the data if you don't want to.

alter table t drop constraint ck ;
alter table t add constraint ck check (n < 0) enable novalidate;

The enable novalidate clause will force inserts or updates to have the constraint enforced, but won't force a full table scan against the table to verify all rows comply.

How can I alter a primary key constraint using SQL syntax?

Yes. The only way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

how to alter table to add the constraint on a existing column

The syntax is in the documentation:

alter table emp_backup
add constraint chk_job check (job in ('CLERK', 'SALESMAN', 'MANAGER', 'ANALYST', 'PRESIDENT'));

How to alter a table to add a constraint?

Strange, for me it is working as expected:

CREATE TABLE FACILITY (
    FACILITYNAME VARCHAR2(100),
    RATE INTEGER,
    STATUS VARCHAR2(20));
Table created.

INSERT INTO FACILITY VALUES ('f1', 1, 'Open');
1 row created.

ALTER TABLE FACILITY ADD CONSTRAINT STATUS_CHECK CHECK (STATUS IN ('Open','Closed','Reserved','Maintenance'));
Table altered.

UPDATE FACILITY SET STATUS = 'abc';

ORA-02290: check constraint (XXX.STATUS_CHECK) violated

Maybe verify the status of the constraint:

SELECT CONSTRAINT_NAME, STATUS, DEFERRABLE, DEFERRED 
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME = 'FACILITY';

+------------------------------------------------+
|CONSTRAINT_NAME|STATUS |DEFERRABLE    |DEFERRED |
+------------------------------------------------+
|STATUS_CHECK   |ENABLED|NOT DEFERRABLE|IMMEDIATE|
+------------------------------------------------+

Also note, unless you give clause VALIDATE in your ALTER TABLE the constraint does not check existing values! By default only new and updated values are affected by the constraint.

Can you replace or update a SQL constraint?

You could drop the existing constraint, and add the new constraint with the NOCHECK option. This would allow you to add the constraint even though data in the table violates the constraint. The problem with doing this though would be that you wouldn't be able to update existing records without making them pass the constraint first.

ALTER TABLE SomeTable DROP CONSTRAINT gradeRule
GO
ALTER TABLE SomeTable ADD CONSTRAINT gradeRule ... WITH NOCHECK
GO

Although this is possible, its not usually recommended because of the potential problems with future updates of the data.

How to modify a constraint on a SQL Server table from c#?

var removeConstraint = "ALTER TABLE Customer DROP CONSTRAINT Con_First;";
var createConstraint = "ALTER TABLE Customer ADD CONSTRAINT Con_First UNIQUE (Address);";
var removeConstraintCmd = new SqlCommand(removeConstraint, conn);
removeConstraintCmd.ExecuteNonQuery();
var createConstraintCmd = new SqlCommand(createConstraint, conn);
createConstraintCmd.ExecuteNonQuery();

NOTE: conn is your connection

The script strings (top two variables) will be what you need to change but, in summary, you drop the constrain then recreate it.

As a side note, I'd be curious to see why you want to do this. You may have problems in your overall approach as this is not something typically done from ADO .net (depending on your scenario of course).

---

Related Topics