How To Create A Unique Constraint On Composite Columns

May 5, 2008

I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.

Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.

Thanks in advance

John.

View 4 Replies


ADVERTISEMENT

Composite Primary Keys Versus Composite Unique Indexes

Feb 20, 2007

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

View 1 Replies View Related

Unique Constraint On Multiple Columns

Oct 31, 2005

Can you create a unique constraint on multiple columns, or does it haveto be implemented as a unique index?If possible can someone please post some sample code?Thanks,

View 4 Replies View Related

Can't Create Unique Constraint

Nov 3, 2000

I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field)
that allows nulls. The table contains multiple rows with the value of null for
field theology. The documentation says one can create a unique constraint on a
field with all unique value except for null. Here is the error message:

'testtable1' table
- Unable to create index 'IX_testtable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
terminated because a duplicate key was found. Most significant primary key
is ''.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See
previous errors.

Any ideas? I am creating a unique constraint and not a unique index. Is there
some other database option to set to allow this?

.

View 2 Replies View Related

Unique Constraint W Nulls, Compare Columns

Jun 20, 2008

Hi Guys.

I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?

For Example:

This is what I want

ID Column1 Column2
--- -------- ---------
1 5 8
2 5 7
3 null 10
4 6 2


This is what I DO NOT want:

ID Column1 Column2
--- -------- ---------
1 5 5
2 5 5
3 7 7
4 null null


I created a unique constraint between the 2 columns, but it still allows dups within the row. Please help me.

View 1 Replies View Related

Create A Foreign Key To Unique Constraint?

Jul 3, 2015

why it is not possible to create a Foreign key to a Unique constraint?

Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.

He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;

The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.

Ok - these two columns ar no PK but the hold an Unique constraint......

View 2 Replies View Related

Unable To Create Unique Constraint On A NULL Column

Apr 5, 2004

Hi all,

I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.

CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.

Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.

Any help would be appreciated.
Thanks,
Amir

View 8 Replies View Related

SQL Server 2014 :: Find Views Which Has More Than 16 Columns For Unique Index / Constraint

Oct 27, 2015

We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...

View 1 Replies View Related

Could Not Create A Foreign Key On 2 Unique Columns

Feb 12, 2006

Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.

View 4 Replies View Related

Create Unique Index On Multiple Columns

Jan 19, 2008

Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
 
I dont care about one column having duplicate values, I want to protect against  Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?

View 4 Replies View Related

Composite Primary Key Constraint

Mar 21, 2007

Hi All,

I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:

alter table databasesize
add constraint pk_dbsize primary key (dbid)

But I get the error message:

Table 'databasesize' already has a primary key defined on it.

How can I do this?

View 2 Replies View Related

T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table

Sep 15, 2014

I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.

View 4 Replies View Related

Foreign Key Constraint To A Composite Primary Key

Mar 6, 2008

I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?

View 10 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

View 1 Replies View Related

Unique Constraint Error When There Is No Constraint

May 13, 2008

We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.

The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,

The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?

View 3 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?

Which one do one use ?

thanks
sonali

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?

View 2 Replies View Related

How To Create Reference For Composite Key

Oct 29, 2007

Hi All, Can anyone tell me how to create a reference for composite key. For ex, I have created tblEmp table successfully. create tblEmp( empId varchar(100), RegId varchar(100),  empname varchar(100),constraint pk_addprimary key(empId, RegId) )   And now, I am going to create another table which references the composite key.create table tblAccount(  acctId varchar(100) primary key,  empId varchar(100) references tblEmp(empId),  RegId varchar(100) references tblEmp(RegId)  )  But it gives error like  Server: Msg 1776, Level 16, State 1, Line 1There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors.  Could anyone please let me know how to create reference for composite key. Thanks in advance,Arun. 

View 4 Replies View Related

How To Create An Table With Composite Key?

May 4, 2005

Hi all,
well i want to have an web-based database application in which in an single webform , i need to insert the values to 5 different tables.
here empid is primary key (PK) in one table and in the rest of the tables its an Foreign Key(FK)
my question is, how can i create these tables with composite key?
ie, i want to creat an table which will have FK and as well another key which both will uniquely idenitfies a row.
Well i am using SQL server 2000 and i wish to create these tables using the enterprise manager.
here are the tables
1) empidtable
empid,fname,lname
2)empcountrytable
empid,country
3)empvisatable
empid,visa
4)empdoctable
empid,doc
5)empfile
empid,filename,filecontenttype,filesize,filedata
Plz do help me
thanx in advance
 
 

View 3 Replies View Related

Add UNIQUE Constraint

May 13, 2006

Hi,I want to add unique contraint to EXISTING column in EXISTING table.I have ms sql 2005.How to do that?

View 7 Replies View Related

Unique Constraint

Nov 19, 2001

Does anyone have any Idea on how I could enforce a unique constrait across multiple tables?

View 1 Replies View Related

Unique Constraint

May 19, 2004

What is the simplest way to add a unique constraint on a field of type varchar(7) that can allow any number of <NULL>'s?

I only want to ensure that when this field is updated, it is updated with a value that has not been used.


IF EXISTS (SELECT Project FROM tbProjects WHERE Project = @cProject)
RAISERROR('Project number already used!',16,1)
ELSE
UPDATE tbProjects SET Project = @cProject WHERE ProjectID = @iProjectID
GO


Also, I cannot allow the user to chante the project field value once it is set.


Any suggestions?

Mike B

View 14 Replies View Related

Unique Constraint

Apr 4, 2008

Anybody knows how to make two columns in a table unique?

View 1 Replies View Related

UNIQUE Constraint

Aug 16, 2006

i'm just creating a table, no data as yet.

i have a varchar(15) fld with Nulls NOT allowed

i want to create a constraint that this field named ipkey be UNIQUE.

I'm having trouble with the syntax for the constraint expression.

i've typed in the SQL Server Managment Studio Express Expression field:
UNIQUE(ipkey)
and
@unq_ipkey UNIQUE(ipkey)

SQL Server Managment Studio Express complains:
"Error validating constrating, do you want to edit expression"

So - what am i doing wrong?
And thanks for taking the time to help.

View 2 Replies View Related

Unique Constraint

Dec 3, 2007

Hi,

I have a table with two column, c1 and c2. c1 is set as primary key. I want c2 to be set with unique constraint.

I choose this talbe in object explorer, right click and select modify. Then I choose "index/key" from "table designer" menu.

The problem is that in the "index/key" dialog, the "Columns" item (under General) is always c1. if I click the "..." button to popup "index column", I could only choose either "c1" or <None> under "column name" dropdownlist.

How could I choose c2 and set unique constraint on it?

Thx
Tao

View 3 Replies View Related

Constraint Unique Row

Apr 11, 2008



Hi,

I need to define a constraint , to prevent some fields of a table to be duplicated, How can i define this constraint ?

View 3 Replies View Related

Create Foreign Key Using Composite Primary Key

Jul 24, 2014

I am trying to create a FK using a composite PK and here are the details that I want to achieve.

Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).

Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).

When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.

View 1 Replies View Related

A Unique Unique Constraint

May 1, 2008

Here is the table I created:

create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go

insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))

select *
from Test

I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:

-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))


Thanks for you help!

Regards,
Anand

View 11 Replies View Related

Sql Exception Unique Key Constraint

Oct 25, 2007

 
Hi All,
I am trying to catch a specfic unique key constraint in a table.
i my table i have two fields USERID And EMAILID and i set both to unique.
now on registration form i am checking that USERID or EMAIID is already present or not.
by taking ex.number =2627 i am not able to find which unique key constraint is getting violated.
is there any other way to find it.
thanks in advance.

View 1 Replies View Related

Unique Constraint Question

Sep 12, 2003

when we use Unique constraint over a column it allows null values.
but then null values are duplicating, how is that?

thanks

View 3 Replies View Related

Unique Constraint With Conditioning?

May 4, 2005

Hi All:

Is there a way to create a unique constraint with 4 fields combined, such as colA, colB, colC, and colD.

but colD is for all the values but value = 3.

If this does not work out, trigger probably is the last way I would like to approach. and it is slower than constraint with many inserts.

thanks
David

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved