117

In addition to the points in other answers, here are some key differences between the two. Note: The error messages are from SQL Server 2012. Errors Violation of a unique constraint returns error 2627. Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value ...


73

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'My_Table'; You can also add (to the WHERE clause) if you have more than one ...


57

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


51

Check the CREATE TABLE page of the manual: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. ...


48

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does. It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing. The first one is easy to fix: ...


44

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them. This option has no performance overhead, as the privilege check is a part of any statement ...


43

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types. DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + N' ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name) + ';' FROM sys.objects AS c INNER JOIN sys.tables AS t ...


40

The MSDN documentattion page about ALTER TABLE explains these: ALTER TABLE: modify the table's structure (and some of the possible actions/modifications are): CHECK CONSTRAINT ..: enable the constraint NOCHECK CONSTRAINT ..: disable the constraint There are also additional, optional steps to do while creating/enabling/disabling a constraint: WITH CHECK: ...


36

In other words, you want values in the column subset to be unique among rows where the column type is 'true'. A partial unique index will do that: CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true'; Data type does matter. If the column type is boolean (likely should be), you can simplify: CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (...


36

Install the additional module btree_gist as is mentioned in the manual at the location you linked to: You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after btree_gist is installed, the following constraint will ...


27

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that ...


26

I don't think you have a problem with the relationships. I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa. A good way to understand this is to visualize the ...


26

To sum up the comments: Like @ypercube hinted, you can do it in a single command, which is cheaper and safer: ALTER TABLE distributors DROP CONSTRAINT zipchk , ADD CONSTRAINT zipchk CHECK (length(zipcode) = 6); ALTER CONSTRAINT in Postgres 9.4 or later (like you found) can only change the "deferability" of a FK constraints. So not what you are looking ...


23

You can name the constraint inline: CREATE TABLE tblTest( -- -- Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3, -- ) ; As the CREATE TABLE msdn page shows: DEFAULT ... To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT. In the same page, we can find that the only options for <...


21

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo ( a int, b int, PRIMARY KEY (a,b) ); INSERT INTO foo (a,b) VALUES (1,1); -- -- two child tables to reference it -- CREATE TABLE t_full ( a int, b int, ...


21

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the chicken-and-egg problem of inserting a user and his active email, ...


20

This is a rather simple CHECK constraint: CREATE TABLE my_table ( id serial, attribute boolean, number integer, CONSTRAINT if_attribute_then_number_is_not_null CHECK ( (NOT attribute) OR (number IS NOT NULL) ) ) ; The logic behind the code is that the logical restriction if a then b is written in boolean logic as (not a) or (b). May seem counter-...


18

Foreign keys can be made conditional...sort of. You don't show the layout of each table, so here is a typical design showing your relationships: create table TransactionalStores( ID int not null auto_increment, StoreType char not null, ..., -- other data constraint CK_TransStoreType check( StoreType in( 'B', 'K', 'O' )), ...


18

It boils down to what UPDATE statement does. It's not entirely obvious but your statement is equivalent to this one: UPDATE upd SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' ...


18

Variant 1 Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it: CREATE TABLE taxrate ( taxrate int PRIMARY KEY , standard bool DEFAULT true , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint , ...


17

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if: the column is declared as NOT NULL in the table definition, or the column is protected from nulls by an active, trusted check constraint Here's a simple test table: CREATE TABLE dbo.Test ( Id int IDENTITY(1,1) NOT NULL, DeclareNotNull int NOT NULL, ...


16

SELECT INTO is not going to do this for you, because while it maintains the column names and data types, it doesn't keep a lot of the other aspects of the table, such as constraints, indexes, etc. The only thing it really keeps outside of the columns is the IDENTITY property if one of the columns has it. Right-click the original table in Object Explorer, ...


16

The regex in your question is not entirely unambiguous In most flavors that support Unicode, \d includes all digits from all scripts. Notable exceptions are Java, JavaScript, and PCRE. These Unicode flavors match only ASCII digits with \d. So in many flavours it would match ١١١.١١١.١١١١ (that character being Arabic-Indic Digit One) I'm assuming ...


16

Let's assume the structure of your table is this one: CREATE TABLE log_logins ( user_id INTEGER NOT NULL, login_time TIMESTAMP NOT NULL DEFAULT now(), ip_v4 TEXT /* or any other representation */, ip_v6 TEXT /* or any other representation */, PRIMARY KEY (user_id, login_time) ) ; You can just add a CHECK that guarantees that either ...


16

Can SQL Server create collisions in system generated constraint names? This depends on the type of constraint and version of SQL Server. CREATE TABLE T1 ( A INT PRIMARY KEY CHECK (A > 0), B INT DEFAULT -1 REFERENCES T1, C INT UNIQUE, CHECK (C > A) ) SELECT name, object_id, CAST(object_id AS binary(4)) as object_id_hex, CAST(...


15

CREATE TABLE t ( n INTEGER NOT NULL CHECK (n < 0) ); works in most RDBMS I know. Edit: A comment by @IMSop prompts me to specify why I wrote “most RDMS I know:” it is well known (and very unfortunate) that CHECK constraints aren’t honored by MySQL. In MySQL, you have to use triggers instead. Another option is to switch to MariaDB.


14

You've forgotten about other types of constraint than primary key (also applies to unique, check, and foreign key constraints) but that's basically it. A column-level constraint can only reference the column that it is declared next to. A table-level constraint can reference multiple columns.


13

You are most likely tying to insert a row in a table for which the serial column sequence value is not updated. Consider following column in your table which is primary key defined by Django ORM for postgres id serial NOT NULL Whose default value is set to nextval('table_name_id_seq'::regclass) The sequence is only evaluated when the id field is set as ...


13

Proper solution The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this: CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); ...


13

You can create a NOT VALID CHECK constraint, which will enforce the constraint going forward, but will not check the entire table for validation upon creation. At some later date, you can attempt to VALIDATE the constraint (when a lock on the table is ok) Please review the documentation - Quote below: ADD table_constraint [ NOT VALID ] This form ...


Only top voted, non community-wiki answers of a minimum length are eligible