Hello
I applied a unique constraint to a Libre Base field via the Tools/SQL menu item using the following command:
ALTER TABLE “Customers” ADD UNIQUE (“CustomerID”)
My question is… how do I remove this constraint?
I am very much a novice at databases, Libre Base, and SQL. I am trying to create a database application. I am adopting a ‘learn as I go’ approach… any comments? Any pointers to where I might locate summary/overview info?
Hello,
The syntax to add a UNIQUE constraint is:
ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] UNIQUE (<column list>)
To remove the constraint it is:
ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>
However, in your statement you did not assign a constraint name so the DB did it for you. You need to find out the name it was assigned. In the Base query section this statement will list all the table constraints:
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS
Find the one you added (will have 'UNIQUE" under ‘CONSTRAINT_TYPE’) for the table you added it to, and use that ‘CONSTRAINT_NAME’ in the above DROP statement.
Please note that this is for the default HSQLDB embedded database (you actually didn’t specify). The manual for this can be found here → Hsqldb User Guide
Thank you very much that worked for me. I am using the default HSQLDB embedded database on the assumption that I will be able to separate the data files at some point so I can have more than one user computer access them via the network - perhaps you could confirm my assumption.
@Novice1 You are welcome and glad all worked out. I would not wait to separate data. Look into the servers available to see which fits your needs. The two which I lean toward are PostgreSQL and Firebird but there are reasons for each individually. You should be looking at this now as it may change your design approach. The embedded HSQLDB engine is very old and other databases will have definite SQL enhancements.