Unique Which Allows Multiple Nulls
Jul 16, 2006Hi,
I have a non-cluster index on the filed, I need to make it non-cluster index and unique which allows multiple nulls, how should I do this with an sql statement?
ADVERTISEMENT
Unique Key's And Multiple NULLS?
Jul 10, 2001Hi,
I have 3 columns that I'd like to set as a key, however I do have multiple instances of NULLs for all 3 columns. Thus when I try to create the key, it stops because there are multiple instances of NULLs. I looked at using procedure that check each entry made into the table, but I couldn't figure out how to deny an INSERT/UPDATE request. Anyone have any better methods of implimenting the unique key?
Ilya
Unique Constraint With Nulls?
Jun 8, 2007Hi,
I have a column in a table in SQL Server 2000 that needs to be unique while ignoring nulls. In other words, more than one record can have nulls, but all non-null values in that column must be unique. When I try to create a unique constraint on this column, the system complains that it can't do it because of duplicate data. The only thing that is duplicate are the nulls. It seems that SQL Server (2000, anyway) considers Nulls when enforceing uniqueness. Does anyone know how to get around this?
Thanks!
Unique Constraint W Nulls, Compare Columns
Jun 20, 2008Hi 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.
Multiple NULLs In A Table
Apr 5, 2004To make the long story short, we have a situation where there can be different datatypes for a column. For example, 'AttributeValue' can be a datetime, int, float etc. My DBA is suggesting the following design,
tblAttribute
AttributeId, AttributeDataTypeId, AttributeValueInt, AttributeValueFloat, AttributeValueDateTime etc
For any AttributeId, only one of the value columns will have a value and the rest will be NULLs. I feel that this is a bad design. (FYI, We decided against SQLVariant for specific reasons). I have suggested, we create a seperate table for each datatype, like
tblAttribute: AttributeId, AttributeDataTypeId
tblAttributeValueFloat: AttributeId INT, AttributeValue FLOAT
tblAttibuteValueDateTime: AttributeId INT, AttributeValue DATETIME
etc for each datatype.
Depending on the AttributeDataTypeId in the Attribute table go to the specific table and pick up the value. This way, we don't need to have too many NULLs in a row. Which of the 2 do you think is a better design? And if you agree with me, what would be the points to support it.
Your time is greatly appreciated
Problem With A Join Due To Multiple Nulls
Mar 29, 2006I want to join 2 tables by a unique ID field, but the ID field also hasmultiple NULLS which I do not want to ignore and I fear they will causeduplication.Using TableA and TableB below i will demonstrate the problem.TableATableA.ID Field1 Field21 Paul 1Null John 12 John 1TableBTableB.ID Field3 Field41 25 1Null 32 1Null 23 12 26 1The Table I want isTableA.ID TableB.ID Field1 Field2 Field3 Field41 1 Paul 1 25 12 2 John 1 26 1Null Null John 1 Null NullNull Null Null Null 32 1Null Null Null Null 26 1I think a select distcinct statement with a full outer join may do whatI want, but I'm not certain so want to check.Regards,Ciarán
View 10 Replies View RelatedMultiple Data Types In One Table With Nulls
Sep 25, 2013Our database stores vehicle data in one table, but 3 different types of data are stored in the one table. The table contains all the columns for all 3 types so when you query the table you get at least 3 rows back with null values for all the columns that don't apply to that record. The data is imported to the table when it's updates so there's a possibility that they're updated at different times so they have a different BATCH like:
BATCH TYPE ID RATING INSURANCE SAFETY
300 SAFE 123 NULL NULL A
300 INS 123 NULL YES NULL
250 RATE 123 A NULL NULL
What I'd like returned is:
ID, RATING, INSURANCE, SAFETY
123 A YES A
I'm trying to do a case statement to pull the data down, but I keep ending up with multiple rows because of all the nulls. I tried doing a SUM of the case statement with an ISNULL(SAFETY,0) but I can't SUM char values. I can probably do this with 3 temp tables to load the data that I want for each TYPE into them and then select and join them together, but is there a better way to do this?
Unpivot Multiple Columns With Datetime NULLs
Apr 3, 2007Hi there,
I'm trying to unpivot a table in SSIS: The pivoted table basically looks like
ID DATE1 TEXT1 DATE2 TEXT2
----------------------------------
ID1 D1 T1 D2 T2
...
The unpivoted result looks like
ID DATE TEXT
-------------------
ID1 D1 T1
ID1 D2 T2
€¦
It works, but the one problem I am facing is: If D1 IS NULL in the pivoted table then D1 in the unpivoted table contains some strange value that is neither NULL nor a valid datetime. In a data viewer I get the in the DATE field "Fehler: Die Parameter Year, Month and Day beschreiben eine nicht darstellbare Datetime." (i. e. "Error: The parameters Year, Month and Day form an invalid datetime."). The loading of the OLE DB target yields an error because the DATE value cannot be converted to a valid datetime.
Anybody having a solution to this?
Column Allows Nulls I Want To Change No Nulls Allowed
May 16, 2006When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
Using Multiple Fields As The Unique Identifier
Jun 10, 2008Please see below post
View 2 Replies View RelatedMultiple Null In Unique Column, Is It Possible?
Nov 20, 2007Hi 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?
Unique Constraint On Multiple Columns
Oct 31, 2005Can 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 RelatedHow To Implement Unique Key On Multiple Field
Sep 5, 2006hello guys,
I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?
thank you.
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?
Create Unique Index On Multiple Columns
Jan 19, 2008Hello, 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?
Re-populate A Unique Number Into Multiple Fields
Oct 14, 2004What I need to do is re-populate a unique number into multiple fields,
Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.
Please help!!!!! Below are how the tables are set up and an example of the result.
TABLE 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO
CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO
TABLE 2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO
CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Query goes as follows for table 1:
SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber
Query goes as follows for table 2:
SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber
From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.
Example:Table 1
000001,Jackson,James,Sam,7.6,38,12345,475431212
Example:Table 2
000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212
I hope this helps
Thanks in advance
Query On Unique Records With Multiple Criteria
Oct 19, 2013I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:-
1. Status is 1 @ max (trans_id), paychnl = CC
2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:-
==> 1 means the current paychnl method
==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL
SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL
FROM DBO.PTFF PTFF
JOIN DBO.CHFF CHFF
ON CHFF.ID = PTFF.ID
WHERE
PTFF.TRANSDATE BETWEEN 130501 AND 130831
AND PTFF.TRANSCODE='T522'
AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,
2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B
3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Samples of the result:-
IDSTATUSTRANS_IDPAYCHNLTRANSDATEEFFDATETRANSCODE
51881712CC13082920130920T522
9361164CC13081620140813T522
78531153CC13082020130814T522
8949151CC13081220130801T522
8949251B13081220130801T522
19081455CC13051620131129T522
19082455A13051620131129T522
19081409CC11101920111129T522
19082409A11101920111129T522
19081404CC11092920111129T522
19082404B11092920111129T522
Need Help W/ SELECT From One Table, One Field, Multiple Unique Records
Apr 22, 2006I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith
John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
Thanks for any help you can give.
AJ
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.
Testing Permutations Of Nulls And Not Nulls
Feb 17, 2008is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies View RelatedSQL 2012 :: After Trigger Not Able To Update Multiple Records With Unique Constraints
Jan 15, 2015I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.
Could like to know the reason.
Unique Constraint Doesn't Allow Multiple Null Values In Server?
Jun 2, 2014Why we the Unique Constraint doesn't allow the multiple null values in Sql Server?
View 2 Replies View RelatedERROR - The Row Value(s) Updated Or Deleted Either Do Not Make The Row Unique Or They Alter Multiple Rows.
Sep 8, 2006i am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example:
ID username password
1 bob bob
1 bob bob
1 john john
1 john john
I know this is a fault with ms sql 2005 however how do i fix it?
Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database.
Im not sure how to do it anyway.
I need to change it though as its my admin password and Ive given it out to web design companys
http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx
Can some 1 read the above page and give me full instructions, I dont know what im doing thanks
info@uktattoostudios.co.uk
Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS
May 15, 2008I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)
Jul 5, 2015This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?
Sep 22, 2004A 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 RelatedUnique Constraint Vs Unique Index In MS SQL 2000
Jul 20, 2005HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies View RelatedUnique Constraint And Unique Index, What's The Difference?
Jun 24, 2006What'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?
Unique Index Vs Unique Constraint
Mar 7, 2001Hi 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
Unique Constraint Vs Unique Index
Jan 20, 2006BOL 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 RelatedUnique Index Vs Unique Constraints
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
How To Select Unique Row When Entire Row Not Unique?
Mar 12, 2008I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
What 's Difference Between Unique Key And Unique Index
Nov 13, 2007What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies View Related