A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
19
votes
13answers
2k 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 ...
16
votes
3answers
5k 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 ...
9
votes
3answers
4k views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint ...
8
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 ...
7
votes
6answers
1k views
Tables with hierarchy: create a constraint to prevent circularity through foreign keys
Suppose we have a table that has a foreign key constraint to itself, like such:
CREATE TABLE Foo
(FooId BIGINT PRIMARY KEY,
ParentFooId BIGINT,
FOREIGN KEY([ParentFooId]) REFERENCES ...
7
votes
3answers
3k views
check constraint does not work?
I have the following table.
create table test (
id smallint unsigned AUTO_INCREMENT,
age tinyint not null,
primary key(id),
check (age<20)
);
The problem is that the CHECK ...
6
votes
4answers
322 views
Can this business logic be enforced by a conditional database constraint?
I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
6
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
329 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 ...
5
votes
2answers
151 views
Is there a name for this “restricted one to many” relationship? Can it be enforced in the DB?
In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level.
A standard ...
5
votes
2answers
168 views
Defining constraints in `CREATE TABLE` statements
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
5
votes
2answers
2k 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 ...
5
votes
2answers
127 views
Unique Contraint/Index Based On Values
I have a table that has the following column definitions:
ID (INT, PK)
Name (VarChar)
Active (Bit)
Bunch_of (Other_columns)
Question: I want to have a Constraint on Name/Active such that we can ...
5
votes
1answer
137 views
Oracle: DB *partial* constraint?
I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation.
I currently have a table at hand that records ...
5
votes
2answers
505 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 ...
5
votes
3answers
2k 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 ...
5
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 ...
4
votes
6answers
957 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 ...
4
votes
2answers
128 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
4
votes
3answers
549 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 ...
4
votes
1answer
1k views
How do you create a relationship to a non-primary key in SQL Server?
I have a Users table which has two columns a primary key called UserID and another column called UserName.
UserID (int) PK
UserName (varchar(256)
They are both unique but I decided for reasons to ...
4
votes
1answer
3k views
Optimal way to ignore duplicate inserts?
Background
This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:
-- Ignores duplicates.
INSERT INTO
db_table ...
4
votes
2answers
377 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 ...
4
votes
2answers
141 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
3
votes
2answers
786 views
Having a unique ID for 2 different tables
We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'.
The ID is therefore unique per both tables ...
3
votes
2answers
109 views
Finding violations of the symmetry constraint
Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
3
votes
1answer
1k views
How to write a check constraint to disallow empty varchar fields?
On SQL Server not nullable varchar columns can hold empty strings.
Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL.
If you design a database schema suitable for both RDBMS ...
3
votes
2answers
2k 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
...
3
votes
2answers
3k views
What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?
I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-)
I ...
3
votes
2answers
221 views
Maintaining referential integrity in a booking system
I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.
I have 3 tables (simplified for this question).
Table 1- CourseSize table- this determines the ...
3
votes
1answer
1k views
What does 'ibfk' stand for in MySQL?
If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
3
votes
1answer
120 views
Restrict record to be update or deleted according to dates inside row for SQL Server 2005
I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher.
I now know that if someone updates, ...
3
votes
1answer
102 views
Symmetric self-referential many-to-many
I'm trying to model a hierarchy of categories where a category can have multiple parents ( an overlapping tree model as described in this book )
I have the following tables
video_categories
int ...
3
votes
1answer
126 views
I need help using hierarchyid datatype and requiring a unique constraint on a varchar field
I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had.
CREATE ...
3
votes
4answers
349 views
MySQL unique constraint across two columns
I have a table defining relationships
Src smallint(5) unsigned NOT NULL,
Dst smallint(5) unsigned NOT NULL,
other fields
I need to add a constraint that says if a given values is present in one of ...
3
votes
3answers
1k views
Altering nullability of column in SQL Server
Say I have the following table:
CREATE TABLE test(test_id int not null identity primary key,
field1 int not null,
field2 int
);
CREATE INDEX IDX_test_field1 ON test(field1);
CREATE INDEX ...
3
votes
3answers
315 views
Need to add a Table Level Constraint to prevent bad data - Constraint is not working
I have a table that has a flag that can be set to 0 or 1. I need to make sure for every claim record in that table that the flag is only set 1 once. So in other words I may have multiple records for ...
3
votes
1answer
1k views
MySQL get next unique value without auto increment
I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100.
WorkorderID ...
2
votes
2answers
171 views
Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures
This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures.
I have the following tables setup.
I normalized the tables to avoid using ...
2
votes
2answers
220 views
SQL set allowed values for a column
I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only ...
2
votes
1answer
78 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 ...
2
votes
2answers
67 views
Constraints based on other columns
Is it possible to limit what values are allowed in a coulumn based off of other values in the row?
For example, my table:
ID Test_mode Active
-- --------- ------
1 1 Null
2 0 ...
2
votes
1answer
475 views
Will FK ON UPDATE CASCADE work?
Intuitively, adding the option ON UPDATE CASCADE to a foreign key constraint will have the effect of updating all referencing columns with the updated value of the key. Would that really work?
...
2
votes
2answers
1k views
SQL Server : how to drop all constraints on a table in T-SQL
I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.
Is there any way to do that with T-SQL? I cannot find any solution ...
2
votes
1answer
75 views
Constrain input to a few different strings
Hi I can't seem to get a constraint working the way I expect in postgreSQL.
From within pgadmin I execute the following SQL query.
-- Check: "TypeCheck"
-- ALTER TABLE "ComLog" DROP CONSTRAINT ...
2
votes
2answers
46 views
Postgresql constrains on FK
I am trying to design a (part of a) database which has to accomplish the following:
There is a students table, containing a bunch of students.
There are educations in the database.
Each student can ...
2
votes
3answers
210 views
SQL Server : explicitly create an index on a primary key and unique fields
UPDATED:
I'll make the question clearer as the first answer isn't quite what I was looking for.
How can I execute this create table statement and ensure that the two automatically created indexes ...
2
votes
1answer
458 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 ...
2
votes
1answer
153 views
PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
In PostgreSQL 9.2.3 I am trying to create this simplified table:
CREATE TABLE test (
user_id INTEGER,
startend TSTZRANGE,
EXCLUDE USING gist (user_id WITH =, startend WITH &&)
);
...
2
votes
1answer
81 views
Is it possible to check all constraints in a MySQL database?
MySQL offers the ability to temporarily disable the foreign key checks, thus allowing inconsistent data to enter the database.
Is it possible to check an entire MySQL database, table by table, row by ...
