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

learn more… | top users | synonyms (1)

0
votes
0answers
17 views

Cannot drop constraint due to FK references

I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
6
votes
2answers
3k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
0
votes
1answer
180 views

Email Column Constraint to Contain '@' character

How do you create an inline constraint for a Email Address Field that requires there be an "@" character? Pretty much I want there to be a restriction to the field so that the data entered must have ...
1
vote
1answer
43 views

Limit the number of records depends on the part of primary key

I created database for football matches. I have 2 tables with a many-to-many relationship, so I also created a junction table: Referee CREATE TABLE referee ( id_referee INT IDENTITY PRIMARY KEY, ...
0
votes
1answer
41 views

MySQL InnoDB constraint does not work

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it. I have tables with data. Below listed their structures: CREATE TABLE `contents` ( `id` int(10) unsigned NOT ...
3
votes
1answer
26 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
1
vote
1answer
35 views

SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is: SQL Error: ORA-12991: column is reference ...
0
votes
1answer
21 views

complex mysql constraints over foreign keys

It seems rational to me to stop users or bad codes from inserting invalid data, but I don't remember to see this anywhere! Consider the following tables How I can make sure an order is always ...
1
vote
1answer
37 views

Read only postgres table, except by trigger

Suppose the following situation: I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock ...
0
votes
1answer
42 views

Don't know why MySQL is not creating table with primary key constraint [closed]

I can't understand why MySQL showing this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...
0
votes
1answer
2k views

What is the difference between covering and overlapping constraints use in DBMS?

What is the difference between covering and overlapping constraints use in DBMS?
0
votes
1answer
23 views

Constraint to prevent overlapping ranges in an array field in PostgreSQL

If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
2
votes
1answer
33 views

sqlite3 foreign keys ignored

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data: BEGIN TRANSACTION; CREATE TABLE ...
4
votes
1answer
97 views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
3
votes
1answer
58 views

Constraint to prevent overlapping Time periods

I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0). I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, ...
1
vote
1answer
47 views

Check constraint

check (phone like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') This is to check the phone number whether it has exactly 10 digits. But I have no idea about placing number as '[0-9]'. It ...
3
votes
4answers
4k views

Unique constraint across two columns

I need to add a constraint with two columns that says if any given value is present in one of the columns, then: 1) It cannot be duplicated in the same column. 2) It cannot be duplicated in the ...
1
vote
1answer
14 views

Deleting data from some but not all tables

I've got a moderately large sql server db for which some data is imported from an external system, and some is entered through the front end or set up when the app is set up. I want to delete all the ...
0
votes
1answer
29 views

Constraint Information - PostgreSQL 9.2

I was trying to delete some data from the table dm.billables: 1 - Select the id - PASS SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306; 2 - Delete the data from the ...
2
votes
1answer
65 views

Add a primary key constraint to an existing unique column

I've seen lots of information around adding a new primary key column to a large database table, but I have a somewhat different situation. I have a large table (~90,000,000 records), that has no ...
0
votes
1answer
15 views

Adding field constraints on child tables based on parent table

I need to add constraints to a particular field in all the entries of a child table with a foreign key to a single row in parent table. What is the best way to do it? For example, my parent table has ...
0
votes
0answers
35 views

Reset default value for many foreign keys in SQL Server

I need to reset the foreign keys default values to null for a big database by using some sql queries. My research in the stackoverflow website got me to drop the existing default values contraints and ...
2
votes
1answer
57 views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
3
votes
1answer
151 views

Multi-tenant database constraints

I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other. My question is, is there any sort of way that I can use ...
5
votes
2answers
543 views

Foreign Keys doesn't work as I expect

Here are my tables; CREATE TABLE `borclular` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tc` char(11) COLLATE utf8_turkish_ci NOT NULL, `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL, ...
2
votes
4answers
178 views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
4
votes
3answers
15k views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
5
votes
2answers
356 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
1
vote
0answers
43 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
4
votes
1answer
27 views

Enforcing that a user should have one or more email addresses

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint. The tables so far are simple: ...
4
votes
1answer
43 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
12
votes
1answer
5k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
1
vote
2answers
656 views

With MS SQL Server are the generated constraint names predictable?

When you create your primary key, and foreign key constraints in a CREATE TABLE script, there are named constraints that are created, such as FK__RecentlyVi__ScId__2764765D ... are these constraints ...
15
votes
3answers
16k 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 ...
1
vote
1answer
87 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 ...
0
votes
1answer
68 views

Postgres constraint function on a single column

Is it possible to create a constraint in Postgres that will run a function, but only if a specific column is changed? Right now, I have this: ALTER TABLE public.employee ADD CONSTRAINT ...
4
votes
1answer
3k views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
33
votes
2answers
16k 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 ...
10
votes
5answers
672 views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
0
votes
1answer
82 views

How to add a check constraint in Postgres without locking the table?

I would like to add a check constraint to a very large table. Something like: ALTER TABLE accounts ADD CONSTRAINT "nonnegative_balance" CHECK balance >= 0; Unfortunately Postgres (9.3, at least) ...
0
votes
1answer
55 views

is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet ...
0
votes
1answer
34 views

Modify table constraint in PostgreSQL

Say I have a constraint ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); that needs to be modified so that zipcode length is 6. Do I have the drop the constraint ...
8
votes
2answers
123 views

SQL Server equivalent of Oracle USING INDEX clause

Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle? Specifically for the construct: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT ...
0
votes
1answer
39 views

Adding multiple constraints

I am trying to add constraints and I think i have an error in the code as its saying i cant add mutlple constrints. What is best way to add mutlple constraints I am using MSQL Server 2008 r2 CREATE ...
2
votes
0answers
53 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. ...
1
vote
1answer
36 views

What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

I have a mysql database that has 250 tables. I would like to run a query to retrieve: tableName ; FieldName ; ConstaintName (Primary or foreign key). I could use ...
0
votes
1answer
26 views

Assign value to a field after checking a condition

I have this query: ALTER TABLE MERCHANTS ADD COLUMN merchant_image bytea, ADD COLUMN merchant_logo bytea, ADD COLUMN merchant_address VARCHAR(100), ADD COLUMN merchant_phone VARCHAR(50), ...
2
votes
1answer
961 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` ...
0
votes
0answers
13 views

Unique attributes as a group [duplicate]

create table test (a int, b int); I want to make a and b be unique, but as a whole. Example: a b - - 1 1 1 2 - this is ok, because even if the previous entry was 1, the b value is different 2 1 - ...
1
vote
1answer
35 views

Duplicate check constraint on multiple tables?

I have an integer value for a data to be included into two or three tables, Say it is part no. The integer value of part no must be between 1 to 8. I want to add that constraint into the tables. ...