A declarative mechanism such as a check or foreign key that enforces some data integrity rule in a database.
1
vote
2answers
25 views
Table restraints to limit value insertions based off of other values in a row
Using SQL Server 2008 R2
Is it possible to limit what values are allowed in a coulumn based off of other values in the row.
Ex:
myTable:
ID, Test_mode, Active
1 , 1 , Null
2 , 0 , 1
...
-1
votes
0answers
30 views
information_schema.table_constraints doesn't exist error in MySQL
I'm new to this and Googling didn't help me with this one. I'm trying to check whether a foreign key constraint already exists like this:
$sql = 'SELECT `constraint_name`, `table_name` FROM ...
0
votes
0answers
25 views
Defacto way of fixing constraint issues without downtime?
I come across two constraint issues all the time (UNIQUE, and referential integrity constraints) within InnoDB. My question is when these issues arise reproducibly what is typically the solution? I'm ...
0
votes
1answer
49 views
Creating a partial unique constraint for MySQL
I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.
My ...
4
votes
1answer
86 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 ...
0
votes
1answer
111 views
Stored Procedure Create Table from Variable with Variable Constraint
I have managed use a store procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure.
The problem:
The ...
0
votes
1answer
63 views
Getting error while importing large data through csv
I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.
I want to disable all of the database constraints temporarily - I do not know how to do this.
0
votes
1answer
39 views
H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not
I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition.
However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the ...
4
votes
2answers
104 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 ...
2
votes
1answer
107 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 &&)
);
...
5
votes
2answers
160 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 ...
1
vote
1answer
77 views
SQL Azure: Drop Constraint and Index
This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake.
Here is my question:
When dropping Constraints, are their indexes dropped as well?
...
1
vote
2answers
122 views
Only one NULL for one part of primary key
I have two date columns : "start date" and "finish date".
I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
0
votes
1answer
95 views
Microsoft Access - performance difference between index and constraint?
I've looked around a bit but haven't come across anything talking specifically about MS Access. So: what, if any, is the performance difference between a field with a constraint and a field with an ...
3
votes
1answer
91 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 ...
1
vote
1answer
65 views
ERROR: Error 1005: Can't create table 'progetto.museo' (errno: 121)
I've found on the web that:
You will get this message if you're trying to add a constraint with a name that's already used somewhere else
and
If the table you're trying to create includes a ...
2
votes
1answer
69 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 ...
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
116 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 ...
2
votes
2answers
185 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 ...
1
vote
1answer
39 views
How to constrain data
I have 3 tables which are related and a constraint on one of them which I can enforce in software but I don't know how to enforce in the database. I have a series of providers who send a set of data. ...
3
votes
4answers
293 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 ...
2
votes
1answer
113 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, ...
0
votes
1answer
53 views
Is it any potential problem with having 2 constraints suppored by one index in Oracle?
I have the following table :
CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, ...
3
votes
2answers
107 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 ...
1
vote
2answers
397 views
Oracle Procedure to Enable/Disable all constraints
I have this nice piece of code:
begin
for i in
(
select constraint_name, table_name
from user_constraints
where constraint_type ='R'
and status = 'ENABLED'
) LOOP
execute immediate ...
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 ...
1
vote
1answer
348 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 ...
2
votes
1answer
430 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?
...
6
votes
4answers
313 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 ...
1
vote
2answers
199 views
Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?
everything is in the question.
I have a table named tableA on which we have indexes and foreign keys and constraints.
I have to modify content in a column called column1. This being a production ...
2
votes
2answers
931 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
71 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 ...
0
votes
1answer
87 views
How to set unique columns combination
see this table:
CREATE TABLE IF NOT EXISTS `default_messages_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`owner_user_id` int(11) NOT NULL,
`to_user_id` ...
1
vote
1answer
913 views
disable constraints before using pg_restore.exe
When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:
ERROR: insert or update on table "someTable" violates foreign key constraint ...
1
vote
2answers
128 views
How do I create a rule for a Primary Key
I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together:
p001
b201
I know I need to create a rule and bind that rule to the primary ...
1
vote
2answers
311 views
UDF in check constraint — downside?
I have a table where all actions affect single rows (insert and update, no delete's but if there were, they would be individual as well). I'm considering using a UDF in a check constraint so I can ...
2
votes
3answers
202 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 ...
5
votes
2answers
122 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 ...
2
votes
2answers
166 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 ...
3
votes
3answers
301 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 ...
1
vote
3answers
165 views
is id and phone number enough to uniquely identify a row in phone numbers
Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the ...
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 ...
1
vote
0answers
84 views
Dealing with Invisible Referencers
Using Oracle database, suppose I have a parent table parent defined in schema A, and a child table child defined in schema B. To achieve this, schema A granted references privilege on parent to schema ...
1
vote
1answer
847 views
Why do my ON UPDATE/ON DELETE rules disappear?
I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working?
I am ...
5
votes
2answers
146 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
1answer
127 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 ...
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
205 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 ...
