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


ADVERTISEMENT

Unique Constraint And Null?

Feb 27, 2008



Can I create a unique constraint on a column that can contain null values?
I need the control of the non null values (must be unique)...null is Ok if there's more than one.
I tried creating a unique constraint but i'm getting the error (duplicate keys <null>)

View 20 Replies View Related

Unique Constraint Does Not Permit Duplicate NULL Values

Oct 2, 2000

After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.

According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.

The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)

Thanks for any and all suggestions

View 2 Replies View Related

Can&#39;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 Doesn't Allow Multiple Null Values In Server?

Jun 2, 2014

Why we the Unique Constraint doesn't allow the multiple null values in Sql Server?

View 2 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

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 View Related

Unique Column Constraint

Jun 6, 2008

I like to use the management console to design tables, but have
been unable to figure out how to add a unique column constraint.

I have a primary key defined in col1 and want to make col2, char(10),
unique, but keep col1 as the primary key.

I have tried the Check constraint menu path, but do not know how
to write the expression.

If I go along the Indexes/Key path, I am forced to change the primary
key which I do not want to do.

Can anyone help?

TIA, Joe

View 5 Replies View Related

Unique Constraint On Nullable Column

Jul 16, 2004

I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.

View 6 Replies View Related

How To Drop UNIQUE CONSTRAINT On Only One Column

May 5, 2014

I am very new to sql and I have got this doubt.

CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };

Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.

if i write

ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;

will drop constraints on both columns which I dont want.

Any query that remove UNIQUE CONSTRAINT only on DESIGN column.

and one more clarification,

ALTER TABLE employee
MODIFY SALARY INT NOT NULL;

ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;

which of the above two is right query to add NOT NULL constraint to the above employee TABLE?

View 1 Replies View Related

Unique Constraint Based On Value In A Column

Nov 23, 2007

I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?

Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A

only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..

are there other solutions to make a constraint like this ?

Aeneas.

View 1 Replies View Related

Unique Constraint To A Column From Another Table

Oct 29, 2005

Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed

View 3 Replies View Related

UNIQUE Constraint On VARBINARY Column

Nov 17, 2006

Hi,

I have a table with one of its column VARBINARY(MAX).

I want to make sure that the values in this VARBINARY(MAX) column is unique.
SQL Server doesn;t allow to create Unique Constraint over VARBINARY fields - whats the best workaround for ensuring uniqueness on VARBINARY columns.

Thanks,
Loonysan

View 1 Replies View Related

T-SQL (SS2K8) :: Cannot Define Primary Key Constraint On Nullable Column But Column Not Null

Sep 30, 2014

We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-

/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](

[Code] ....

The script I am using is

DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script

[Code] ....

When I the the script I get the error message Could not create constraint. See previous errors.

Looking at the strings I build

ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] 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]

They all seem fine except the last one which returns the error

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

None of the fields I try to create the key on are nullable.

View 2 Replies View Related

SQL Security :: Enforcing Unique Constraint On A Column Encrypted With Cell Level Encryption?

May 11, 2015

I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!'
    CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs'
    CREATE SYMMETRIC KEY ERKEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ERCERT

[Code] ....

The output makes it obvious why the constraint has 'not' been enforced.

        Email
       -------
    1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
    2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
    3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB

But, how do I work around this?

View 8 Replies View Related

Multiple Null In Unique Column, Is It Possible?

Nov 20, 2007

Hi All,



I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);
I get an error duplicated keys on the unique column.

In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.

I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...



Are there any ways to archieve that in sql sever compact?

View 2 Replies View Related

Multiple Null In Unique Column, Is It Possible?

Nov 20, 2007



Hi All,

I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);

I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.

I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...

Are there any ways to archieve that in sql sever compact?

View 5 Replies View Related

Stuck Between Cannot Insert The Value NULL Into Column 'ID' And Violation Of PRIMARY KEY Constraint

Jan 20, 2004

Cannot find an answer to this in previous posting, though there are similar topics.

My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:

Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.

However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:

Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.

Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.

Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.

Please help!

View 5 Replies View Related

Creating A Unique Constarint On A Multiple Null Column

Aug 3, 2007



HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

View 1 Replies View Related

Create Constraint To Add Only Alphabet In Column

Aug 21, 2006

I need to create constraint in column to add only alphabet .

like "adc" ,"sdfsd" and not "1234adfd".plz reply soon.

View 4 Replies View Related

SqlExpress Create Unique Column

May 2, 2006

Hi, can anyone guide me on how to create a unique field with SqlExpress and VC#.net?

I know how to create primary key with identity, however I need to create another unique column for "email". I have search high and low for a solution but found none, the closest i got is unique constraint which I've not idea what's that.

Please help, else I have to always do a duplication check before inserting new records.

Many thanks.

View 6 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

SQL 2012 :: Create A Check Constraint Where Another Table Column Value Is Also Involved

Mar 12, 2015

Table ATable B
AIDBID
Col 1BCol1
Col 2
Col3

I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1

How can I achieve this without using function.

View 8 Replies View Related

How To Create New Column Indexed And Unique In Existing Tbl - 2005

Mar 7, 2008

Need help,

I'm a newby to SQL and looking for how-to-help.
I have an existing DB and within a certain table, I have created a new Column via Studio manager, but need help with the following:

Need to make the new Col "Unique, and Indexed" but cannot see anywhere in studio manager interface to do it.

Thanks.

View 12 Replies View Related

Named Constraint Is Not Supported For This Type Of Constraint (not Null)

May 13, 2008

Hi, all.

I am trying to create table with following SQL script:





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);

When I execute this script I get following error message:

Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]

I looked in the SQL Server Books Online and saw following:

CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]

As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.

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

DB Design :: Unable To Create Index After Column Resizing

Oct 5, 2015

We have a vendor created database with 9000+ tables, one of which has about 6 billion rows.  The vendor redesigned the database recently and ever since we've had terrible performance.

What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.

Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.

Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.

The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).

There is no additional data, just wider columns. Why we are unable to create the index?

What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?

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







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