Can You Replace or Update a SQL 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 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 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>)

execute constraint on update sql

You can do it using a trigger like this:

CREATE TRIGGER updTableDate
ON [Table] 
AFTER INSERT, UPDATE   
AS
BEGIN
SET NOCOUNT ON;
    UPDATE [Table] SET modified = GETDATE() WHERE [primary_key] IN (SELECT [primary_key] FROM INSERTED)
END

Or better:

CREATE TRIGGER updTableDate
ON [Table] 
AFTER UPDATE   
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [Table] t SET modified = GetDate() FROM INSERTED AS i WHERE t.id = i.id 
END

Update to replace specific value with another except when it already exists

You should correlate the subquery:

UPDATE MyTable AS t1 
SET lastname = 'woo' 
WHERE t1.lastname = 'poo'
  AND NOT EXISTS (SELECT 1 FROM MyTable t2 WHERE (t2.firstname, t2.lastname) = (t1.firstname, 'woo'));

And then delete the remaining rows:

DELETE FROM MyTable WHERE lastname = 'poo';

See the demo.

Modify a constraint in Oracle

No.
Create a new constraint first and then drop the old one.

Solutions for INSERT OR UPDATE on SQL Server

don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.

When multiple threads will try to perform Insert-or-update you can easily
get primary key violation.

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

To avoid deadlocks and PK violations you can use something like this:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

or

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

---

Related Topics