SQL Server table design - don't want two records the same

Question

One of the tables in my database has three columns.

They are dt_id (PK), dt_name (varchar) and u_id (FK).

dt_id is a identity column.

But I want to have so that dt_name can only be once per user.

So you can only put in " dt_name='CC', u_id=1 " once.

How can I set the dt_name column so ?

----Edit:----

Sorry, i will explain better..

dt_it is just a id on the table. dt_name is the value field u_id is the id on a user.

I want to be able to add dt_name unique on each user, so u_id=1 could have value 'CreditCard' only once. But u_id=2 could also have it, but only once..

Hope this clears it up a little.. I'm using MSSQL

Answer 1

Add a unique index for "dt_name".

create unique index ix_dt_name on your_table(dt_name)

[edit] not exactly sure what you're asking; if you want the combination of name & id to be unique, you'd create the unique index on both of the columns:

create unique index ix_dt_name_and_id on your_table(dt_name, u_id)

Answer 2

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueNamePerUser ON tab
(
   dt_name, u_id
)

Answer 3

Just create a unique index on (u_id,dt_name)

CREATE UNIQUE INDEX ix_u_id_dt_name on mytable(u_id,dt_name)

Answer 4

From what I understand is that you only want dt_name to display 'CC' once in your database. If that is the case you would need to create a trigger in your DataBase to achieve this.

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
 { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
    [ WITH APPEND ]
    [ NOT FOR REPLICATION ]
    AS
    [ { IF UPDATE ( column )
        [ { AND | OR } UPDATE ( column ) ]
            [ ...n ]
    | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
            { comparison_operator } column_bitmask [ ...n ]
    } ]
    sql_statement [ ...n ]
 }
} 

link text

The link explains the use of all these. Therefor you can have a check before entering the row to see if the name exists. If it does then run the second script in the trigger.

EDIT **

The other codes are more efficient. but a trigger could still work. However I won't keep digging into it if the others are better suited. Best of luck.