A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.

learn more… | top users | synonyms

0
votes
1answer
840 views

unique key violation occuring on a unique value combination

SQL SERVER 2008 I have a mapping table that contains a deleted date column. Since the table uses a surrogate key as its unique identifier there is a uniqueness constraint on the table. This ...
1
vote
2answers
794 views

SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.) Both databases are identical with many foreign-key constraints on tables. ...
4
votes
3answers
735 views

Making a column immutable in MySQL

I want to make a column in a relation unmodifyable for consistency reasons. The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
1
vote
1answer
1k views

function based index as a conditional unique key?

I have a search table which has a generated primary key PK_ID,and also has primary keys from three or four other tables in the FK_ID column CREATE TABLE METADATA ( PK_ID NUMBER(10) ...
4
votes
6answers
2k views

Any way around unique index 16 column max

According to the CREATE INDEX documentation: Up to 16 columns can be combined into a single composite index key. We've got a table with ~18 columns that need to form a unique combination. This ...
2
votes
1answer
82 views

How to get dependencies order

I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
3
votes
2answers
3k views

How can I help SQL Server recognize my view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
6
votes
2answers
3k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
2
votes
1answer
549 views

Trusted Constraints and NOT FOR REPLICATION

Version and Build: SQL Server 2005 SP4 (9.0.5000) As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a ...
20
votes
13answers
4k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
4
votes
2answers
520 views

How to mark an item as “Expired” when it's ExpiryDate value is in the past?

I have a table of items, each of which has a Status and ExpiryDate. What is the best way of enforcing this: when ExpireyDate > DateTime.Now update Status to "expired" I could have a stored ...
5
votes
3answers
3k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...
8
votes
3answers
2k views

Dropping a constraint (index) on a column

How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
5
votes
6answers
393 views

Running a database without constraints

We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other ...
6
votes
2answers
2k views

Non-unique multicolumn foreign key

I have a "comments" table that models a conversation on a topic, like this: id serial topic_id integer parent_comment_id integer body text So, every comment has a reference to its topic AND ...
9
votes
2answers
2k views

Unique Constraints on Nullable Columns in SQL Server 2005

In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
28
votes
5answers
8k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
6
votes
2answers
715 views

Dropping duplicate|redundant Unique Constraint from FILESTREAM table

I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie: ALTER TABLE [dbo].[TableName] ADD CONSTRAINT ...