FAQ Database Discussion Community


I want to set a Unique Constraint that allows nulls using SQL Server 2014 Express [duplicate]

sql-server,unique-constraint,sql-server-2014-express
This question already has an answer here: How do I create a unique constraint that also allows nulls? 10 answers I want to set a unique constraint that allows nulls using SQL Server 2014 Express. I know this has been asked before but it was asked 5 years ago...

PostgreSQL multiple nullable columns in unique constraint

sql,postgresql,database-design,null,unique-constraint
We have a legacy database schema that has some interesting design decisions. Until recently, we have only supported Oracle and SQL Server, but we are trying to add support for PostgreSQL, which has brought up an interesting problem. I have searched Stack Overflow and the rest of the internet and...

Symfony2 UniqueEntity not working: throws db exception

php,symfony2,unique-constraint
I have a User class as follows: /** * User * * @ORM\Table() * @ORM\Entity * @UniqueEntity("username") */ class User implements UserInterface { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var string * * @ORM\Column(name="username", type="string", length=30, unique=true) */ private...

Return existing primary key ID upon constraint failure in sqlite3

python,sqlite,python-3.x,sqlite3,unique-constraint
I have UNIQUE constraint on two columns of a table in SQLite. If I insert a record with a duplicate on these two columns into the table, I will get an exception (sqlite3.IntegrityError). Is it possible to retrieve the primary key ID of this record upon such a violation, without...

Prevent a value from being entered if it's a prefix of another value

sql-server,sql-server-2008-r2,unique-constraint
How could I prevent a value from being entered that is a prefix of another value in the same column? For example, if MyTable.NumberPrefix already contains abc then ab can't be added. My first attempt (below) was to use an indexed view. But a unique index cannot be created on...

Mongodb unique compound index not honoring Dates

mongodb,date,indexing,unique-constraint,compound-index
I'm having trouble with a unique compound index in mongodb. Code speaks best so (in mongo shell): var collection = db.getCollection('test'); collection.drop(); // start from scratch collection.createIndex({date:1}); collection.createIndex({_id:1, date:1}, {unique: true}); var doc1 = {_id: NumberInt(1), date: new ISODate('2015-04-27 00:00:00.000Z')}; var doc2 = {_id: NumberInt(1), date: new ISODate('2015-04-28 00:00:00.000Z')}; collection.insert(doc1);...

Symfony Custom Validation Constraint pass parameter

validation,symfony2,constraints,unique-constraint
I have a custom validation constraint to check if an entered book name in a form is unique. I cannot use @Unique, because that would also validate the current value of the book and will always throw a validation error, even if I didn't want to change the book name....

Laravel validation and updating a record

mysql,laravel,unique-constraint
In my user table in my database I have a unique key constraint set on email and telephone column to prevent duplicates. My question is: What happens when a user wants to update their records? When they submit the same email does it throw an error. When they try to...

SSRS 2012: How do I return the most recent rows only?

sql-server,reporting-services,sql-server-2012,unique-constraint
Okay, here is my current query: SELECT pk.RxFill.PATIENT_PRICE ,PersonQuotas.QuotaYear ,PersonQuotas.QuotaMonth (and a bunch of other stuff to link them) FROM pk.RxMain INNER JOIN pk.Doctor ON pk.RxMain.PHARMACY_ID = pk.Doctor.PHARMACY_ID AND pk.RxMain.DOCTOR_ID = pk.Doctor.DOCTOR_ID INNER JOIN pk.Formula ON pk.RxMain.PHARMACY_ID = pk.Formula.PHARMACY_ID AND pk.RxMain.FORMULA_ID = pk.Formula.FORMULA_ID INNER JOIN pk.Patient ON pk.RxMain.PHARMACY_ID = pk.Patient.PHARMACY_ID...

An INSERT INTO in MSSQL Server 2005 that groups by a column leads to unexpected duplicate key violation

sql-server,stored-procedures,sql-server-2005,unique-constraint
I am executing a stored procedure on Microsoft SQL Server 2005. The code in question has worked flawlessly for about 12 months, but is now failing on "duplicate key violation error" How is it possible to get a duplicate key violation error with the following? This is the error: Violation...

Rails scoped uniqueness constraint throws error when updating nested attributes

ruby-on-rails,ruby,nested-attributes,unique-constraint,updating
I have an application that creates pages, called campaign pages, from a selection of widgets that can be used to specify the layout of the pages. I'm working on the functionality to load and edit the contents of a campaign page, alter the contents, and update the relevant entries for...

Postgres unique constraint on index does not work

hibernate,postgresql,unique-constraint,spring-jpa
On a quite simple table CREATE TABLE collectionmaster ( id character varying(32) NOT NULL, version integer, publisherid character varying(12), title character varying(1024), subtitle character varying(1024), type character varying(255) NOT NULL, CONSTRAINT collectionmaster_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE INDEX idx_coll_title ON collectionmaster USING btree (title COLLATE pg_catalog."default");...

ORACLE - CHECK constraint enforcing conditional uniqueness

database,oracle,constraints,unique-constraint
I have a table - PROGRAM - which includes the columns PROGRAM_NAME and PROGRAM_NUMBER. PROGRAM_NUMBER is already unique. CREATE UNIQUE INDEX UNIQUE_PROG_NUMBER on PROGRAM(upper(PROGRAM_NUMBER)); There is also a constraint to ensure that either PROGRAM_NAME or PROGRAM_NUMBER is always populated. ALTER TABLE PROGRAM ADD CONSTRAINT NAME_OR_NUMBER_NOT_NULL CHECK (PROGRAM_NAME IS NOT NULL...

UNIQUE constraint where NULL is one valid value [duplicate]

postgresql,unique-constraint
This question already has an answer here: Create unique constraint with null columns 4 answers Inn PostgreSQL I want to have a multi-column UNIQUE constraint where one of the columns can be NULL exactly once. What I've tried so far: ALTER TABLE customexternalemail ADD CONSTRAINT customexternalemail_sp_emaildomain_unique UNIQUE(serviceproviderid, emailprefix, emaildomainid);...

When Would This INSERT.. ON DUPLICATE Fail?

mysql,constraints,unique-constraint
I was troubleshooting an INSERT... ON DUPLICATE KEY statement due to a constraint error, but I irreversibly edited the row in question, and I'm no longer getting the error. I'm pretty sure I edited either request_path or target_path. Some of the values that were already there for certain area: store_id...

Enforcing uniqueness using lightweight transactions in Cassandra

transactions,cassandra,unique-constraint
This is more of a data-modeling question, but it is related to lightweight transactions because it appears that trying to use this feature is having an impact on how I model my data. My specific use-case is that I want to enforce uniqueness on specific fields on INSERT. Cassandra's documentation...

add new column to existing Composite Unique Key

mysql,unique-constraint,composite-key,alter,unique-index
I have table named "room" , and it has the following composite unique key room_acId_levelId_year_name_unique formed from FOUR columns : acId levelId year name And I want add column isLevel to that key without drop and recreate it The new key should like this: room_acId_levelId_year_name_isLevel_unique with the following columns :...

How to make a specific parameter unique in Java

java,parameters,unique-constraint
How can I make sure that each instance of a class will have a unique value for a specific parameter? For example: public Foo(int value); value should be different for each instance of Foo. Otherwise, it can throw an exception.

How do unique indexes really work and avoid collisions?

mongodb,indexing,duplicates,unique-constraint
Suppose I have a collection where I create a unique index on a field: db.users.createIndex({username: 1}, {unique:true}) What happens if two documents with the same username are SIMULTANEOUSLY being inserted in the collection? How does the database prevent the collision? I mean which one gets inserted and which results in...

How to avoid returning the same string twice in a row

java,unique-constraint
I am working on a SimpleEliza chartbox in Java. I have finished everything that my teacher has required except that I need to program the method askQuestion to not return the same string from the array twice in a row. I am uncertain as how to approach this problem. Can...

How to create a unique key on a date chained table

mysql,sql,database-design,unique-constraint
I have two tables : employee and a child table employeeVacation. employee id employeeName employeeVacation employeeId vacationStartDate vacationEndDate employee holds a list of employees and has a unique key on the employeeName column. employeeVacation holds details of each employee's vacation dates. It has a foregin key on employeeId to the...

SQL: How to sort overlapping groups efficiently

sql,sqlite,unique-constraint
I'm trying to make groups on a database with 10.000+ rows. I need to be fast and efficient, so I'm doing binary variables for each cluster. One, Two, Four, Five and Six is in Group1. But 'Two' might also be in Group nr. 2, because of errors I cannot overcome...

Realm and unique objects

android,unique-constraint,realm
I've recently started to play around with Realm for Android, but I can't seem to find any information about whether it's possible to set a unique constraint on a RealmObject, as to avoid duplicates. I'm appending data to an implementation of the RealmBaseAdapter and I don't want duplicates. Is this...