Unique Columns And Logical Deletion

May 31, 2007

In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships. However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for "active" rows. I'm hoping someone can provide some advice on how to best handle this situation.



Here is an example to work from. Suppose we have a table that contains job positions. The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been "deleted". This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company. You can substitute your own scenarios here as there are quite a few.



Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want. However we will want to be able to "delete" a position but later add a new position with the same name. One could argue that we should just "undelete" the original position but that is not often a good idea so assume "undelete" is not an option. Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name. We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.



My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column. The UniqueName column is set (via a trigger) to the Name column when the position is active. When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness. This is handled by the system and not exposed to clients.



Any advice or alternatives on this approach (preferably with advantages and disadvantages)? Thanks.



Michael Taylor - 5/31/07

http://p3net.mvps.org

View 4 Replies


ADVERTISEMENT

Report Model - How To Create A Unique Logical Primary Key In A Named Query

Apr 7, 2008



Hi,
Te following situtation is :

ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).

The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?


View 2 Replies View Related

How To Avoid Non-published Columns Deletion On Subscriptions' Reset

Apr 26, 2007

Hi all!
I have been doing some testing with transactional replication. I have a table (TEST) with the following columns:

[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
[stock] [int] NOT NULL CONSTRAINT [DF_Prueba1_stock] DEFAULT ((0)),

I want the table rows to have an independent stock value for each database. So,
I made a transactional publication with updatable subscriptions, to replicate all the table columns, except the stock column.

The problem is when I reset subscriptions: the table is deleted an created again on the subscriber, so all the data stored at the stock column is lost. I tried to solve this changing the "Action if name is in use" option at the publication. I choose to keep the current object without changes, but I began having problems with the generation of the identity values at the subscriber.

Any suggestions will be welcomed

Thanks in advance ;-)

View 8 Replies View Related

Making 2 Columns Unique With Each Other

Feb 21, 2008

Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record.   Graciaz,Nhoyti 

View 3 Replies View Related

Use Identity For Unique Columns? Or Not?

Feb 13, 2004

I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.

The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.

It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.

Any pro's con's appreciated,

Dan

View 8 Replies View Related

How To Get Unique Record With Columns

Mar 20, 2013

I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)

What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.

So that I don't have duplicate candidate ids and i have single row with all column data.

View 6 Replies View Related

Unique Index On 2 Columns?

Apr 19, 2013

I find to be able to have multiple NULL entries in the following constraint:

Code:

CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;

But is not possible to check multiple columns in "WHERE".

I using SQL Server 2012 Express

View 3 Replies View Related

Designing Unique Columns

Mar 7, 2006

Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.

2. I am using an identity column (INT)to create a unique value within the table

3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)

many thanks in advance

ICW

View 5 Replies View Related

Select Unique Rows For All Columns

May 17, 2007

Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.

Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows

Any thoughts?


Jorge3921

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

Unique Index On X Columns But Not Primary Key

Feb 23, 2006

my table :

CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]

i must keep ID columns as primary key

ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]


but now A1+A2 must be unique

how can i do it ?

thank you

View 5 Replies View Related

Merge Tables On Unique Key Using Different Columns

May 15, 2008

hi guys,

i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text

ID1+ID2+ID3 is the clusterd unique key in each table

what i want:
ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1

what i tried is to get all possible rows with

SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3

but i dont know how to join or add the other columns. maybe with

WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...


any ideas?
thx

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

Unique Index For Two Columns In A Table

Jul 20, 2005

Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.

View 3 Replies View Related

Data Flows And Unique Columns.

Feb 9, 2007

Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.

Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.

What's the recommend method to do this now - since row level processing of variables seem to be a no-no?

View 5 Replies View Related

How To Guarantee Unique Of Columns &&> 900 Bytes

Aug 14, 2006

We have an app that threads together emails coming out of Exchange, using their messageid. To ensure threading works correctly, we need to ensure uniqueness of messageid, which we do with a unique index (we also need to be able to lookup by messageid when a message comes in).

We are currently porting the app from Oracle and PostgreSQL to SQL Server and are having problems with the 900 byte max length of an index. The problem is that the maximum size of a messageid (according to the Exchange docs) is 1877 bytes.

How can we guarantee uniqueness?

View 7 Replies View Related

Selecting The Rows Based Off Of Unique Columns

Mar 18, 2007

Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ...  There will be entries where there can be duplicate customerFirst and customerLast names.  I would like to just return a single entry of the duplicate names and all associated row information.  IE: [customerID], [customerFirst], [customerLast],            [ email],             [program]         01               Bill                 Smith             bill.smith@hotmail.com    ymca        02               Bill                 Smith             bill.smith@hotmail.com    Sports        03               jon                   doe                 jon.doe@hotmail.com    AAA        04               jon                   doe                 jon.doe@hotmail.com    Ebay          05               Paul                 Sprite             paul.sprite@hotmail.com    Rec Desired Returned result:        01               Bill                 Smith             bill.smith@hotmail.com    ymca        03               jon                   doe                 jon.doe@hotmail.com    AAA
         05               Paul                 Sprite             paul.sprite@hotmail.com    Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr);         dAdapter.Fill(pocDS, "Data Set");        However this is throwing up an error when i build the app:  An expression of non-boolean type specified in a context where a condition is expected, near ')'.



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:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.

Source Error:




Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison  1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry  

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

Delete Duplicate Rows With No Unique Columns

Apr 3, 2000

I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?

View 1 Replies View Related

Unique Columns In Sort By Importance Result

Dec 30, 2004

I try to search my data and sort the result by importance.

I'm using a MS Access database and my data (table1) looks like this:


Code:

ID NAME TEXT
1 Apples Good red apples
2 Bananas Fine yellow bananas
3 Yellow apples Great yellow apples


I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:


Code:

SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*'
UNION
SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*'
ORDER BY searchorder


The output is this:


Code:

ID NAME TEXT SEARCHORDER
3 Yellow apples Great yellow apples 1
2 Bananas Fine yellow bananas 2
3 Yellow apples Great yellow apples 2


So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.

Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?

Best regards,
Peter from Denmark

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

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

SQL 2012 :: How To Find Unique Rows Considering Only Specific Columns

Apr 18, 2014

write a query which retrieves only unique rows excluding some columns.

IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14

In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is

IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14

View 4 Replies View Related

Insert Diffrent Columns,Determine Non Unique Fields

Sep 19, 2006

andre writes "I have two questions:

1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??

Thank you!!"

View 1 Replies View Related

Transact SQL :: Generate Unique Username From Data Columns

Oct 19, 2015

I am trying to achieve the below problem statement, however I am getting stuck at the looping part.

EX: We have three columns

First Name |  Last Name | Mothers Name

I want to generate the username field using above columns, so lets consider, I have following data

First Name |  Last Name | Mothers Name
a                    b                  cdef
a                    b                  cdfg
a                    b                  cdfj

Expected Usernames:

1: a.b
2: a.b.c
3. a.b.cd

Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)

View 4 Replies View Related

Integration Services :: How To Implement SCD For No Unique Columns In A Table

May 11, 2015

I would like to know what is the use of business key? Is it necessary to have unique key between source and destination? If no, How can we implement SCD?

PS : My source is CSV files and Destination is Oracle DB?

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

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

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This 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.

View 6 Replies View Related

Deletion

Nov 6, 2007

Hi all,

I have a table in xyz database and there is no column in table like creation_date or modified_date.

The problem is I want to delete records which has been added in the table before 1st jan 2007.

The size of table is 85 GB

Immediate help would be appriciable.

Regards,
Frozen

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







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