How To Ensure Non-null Duplicate Values Are Not Inserted In SQL Server 2005
I have a history database in
SQL Server 2005 that is used to archive data from live database. I need to add a column in history database named RowID INT. This column cannot be made as identity since it need to store values from live database.
Note: In the live database the RowID is identity column. This column was added by me in live database also recently.
I need to make sure that in history database, the non-null values inserted into this column is unique. I understand that in SQL Server, we cannot make a nullable column as unique.
So, in SQL Server 2005, what is the most efficient way to make sure that insertion of
non-null duplicate values into RowID columns throws an error?
This is too long for a comment. It is time to upgrade to a more recent version of SQL Server. Microsoft is discontinuing support in April (2016) (see here).
Starting in SQL Server 2008, you can trivially do what you want with a filtered unique index:
create unique index unq_history_rowid on history(rowid) where col is not null;
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key