Here is my use case. I am using Postgres 13.3 I have a people table under a draft schema that has multiple columns.. email and created_at. The email should have been unique but it was not. If I alter the table to be unique, there were duplicate emails inserted before and I don’t want to lose the data by…
Tag: constraints
Enforce 2 unique constraints on upserts in Postgres
I have a table where I save contacts data I add data to this table from several sources. Some sources have Linkedin profiles data about people, other sources have email data. Sometimes the full names are not equal, even if they refer to the same person. And I want to do upserts to not have duplicated data. Fo…
Putting constraint on an attribute gender so that it only accepts ‘F’ or ‘M’ characters
I am doing an assignment which is implementing a database that has book, author, publisher and people tables in it. I have to put constraint on the gender attribute of my Author table so that the only characters in a component for this attribute can be ‘F’ or ‘M’. I only thought of CHA…
Create CHECK constraint SQL
I hope you can help me. I would like to create a check constraint that ensure the column INKOOPBEDRAG has to be higher than PRIJS_PER_D. In the table INKOOPOVEREENKOMST.[BARCODE] it is a FK to ARTIKEL.[BARCODE]. I have created the following CHECK constraint, but I get an error: Answer I solved this using the …
How do you make your check constraint case insensitive in SQL?
This is my piece of code. I want to be able to insert values in lower case too. Example down Below: But I can edit my check constraints to add ‘Web Site’ or ‘Catalog’, I just want to try something else. Thanks. Answer You may lowercase the column and then compare to lowercase string li…
Passing parameters for a constraint function
I have the following table that joins driver and truck tables in order to assign trucks to drivers. I need to constraint entries that belong to the same driver on the same day, and DO NOT include the same truck code. It was suggested to create a boolean function to pass along dateval, truckval and driverval, …
DROP Constraint without knowing the name
I know there are a lot of references out there for what I am going to mention. I am rather sharing something I feel should be very useful and consolidating in one place – Q&A-style. I have struggled earlier finding this for different constraints on different time. The following constraints are commo…
Postgresql constraint violated at restore
I have a problem with a constraint that is being violated by some rows when restoring my DB, but has never been violated at usage. I have 3 tables : some_table here is another table, which I think is not relevant for this problem. Now what I want is to have a unicity constraint between as.id and bs.some_id th…
I am dropping the with check option constraint from view but it is not being enforced
So I am stuck in a problem. I defined a view with the WITH CHECK OPTION constraint, now i wish to delete it, but even after dropping the constraint i get the error :=ora-01402: view WITH CHECK OPTION where- clause violation. this is the sequence of SQL commands i am executing – I cant figure why this is…
Can I allow only certain specific strings to be inputted for a table in Oracle 10G?
In this table, I want a constraint such that, name inputted should be only ‘trial’,’experiment’ or ‘test’. Answer You can add the CHECK constraint as follows: If you don’t want the null values in the name then you can also use NOT NULL constraint on the name column. W…