CODEWITHSUNDEEP
sqlsql-server
NibblyPig
NibblyPig

Reputation: 52942

I need some help with SQL and constraints

I don'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?

Upvotes: 1

Views: 76

Answers (4)

Alexander Verbitsky
Alexander Verbitsky

Reputation: 520

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)

Upvotes: 0

ain
ain

Reputation: 22749

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;

Upvotes: 0

Alex_L
Alex_L

Reputation: 2654

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.

Upvotes: 0

Max
Max

Reputation: 804

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

Upvotes: 1

Related Questions