Ask Your Question
0

Base - remove field unique constraint

asked 2018-12-14 17:19:34 +0200

Novice gravatar image

updated 2018-12-14 17:28:59 +0200

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-14 17:53:15 +0200

Ratslinger gravatar image

updated 2018-12-14 17:59:16 +0200

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

edit flag offensive delete link more

Comments

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.

Novice gravatar imageNovice ( 2018-12-18 17:45:28 +0200 )edit

@Novice 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.

Ratslinger gravatar imageRatslinger ( 2018-12-18 18:35:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-14 17:19:34 +0200

Seen: 62 times

Last updated: Dec 14 '18