A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
7
votes
3answers
2k 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 ...
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 ...
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 ...
16
votes
3answers
4k 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 ...
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 ...
4
votes
3answers
536 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 ...
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 ...
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
...
1
vote
1answer
354 views
MySQL: Unique constraint on large column
I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.
MySQL appears to ...
4
votes
1answer
2k 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 ...
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 ...
3
votes
2answers
207 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 ...
