I need some help with SQL and constraints

I d开发者_StackOverflow中文版on't know much about SQL at all, but I have a table and it has a unique key constraint which I can see in SSMS by expanding the Keys folder under my table. It makes one column, 'name', UNIQUE.

At the moment it's completely unique, but I need to change it so name is unique for a specific ID in another column, 'catflapID'. So names can be duplicated in the table but for each catflapID there can be no duplicates.

I know absolutely nothing of how to do this, and I need somehow to end up with a script that removes the existing constraint and adds my new constraint.

Any advice?

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[test_table]') AND name = N'IX_test_table')
ALTER TABLE [dbo].[test_table] DROP CONSTRAINT [IX_test_table]
GO

ALTER TABLE [dbo].[test_table] ADD  CONSTRAINT [IX_test_table] UNIQUE NONCLUSTERED 
(
    [c_name] ASC,
    [c_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

you need to create the foreign key in relation-table. For example,

CREATE TABLE table1 (catflapID  INT NOT NULL PRIMARY KEY , ...)
CREATE TABLE table2 (ID INT PRIMARY KEY , catflapID INT NOT NULL FOREIGN KEY REFERENCES Table1(catflapID))

catflapID in table1 is NOT NULL and PRIMARY KEY, so this field will be unique. catflapID in Table2 is referenced from Table2, so it can contain values from Table1 only.

To create new constraint

ALTER TABLE tab
  ADD CONSTRAINT UniqueNameForCat UNIQUE (catflapID, name); 

To drop the old, obsolete constraint

ALTER TABLE tab DROP CONSTRAINT oldConstraintName; 

You should create complex unique constraint with both columns.

Remove existing constraint and execute this code (change "MyTable" name to actual table name)

ALTER TABLE MyTable ADD CONSTRAINT MyConstraint2 UNIQUE (catflapID, name)