Create first the table with unique constraint
create table test (id, geometry UNIQUE);
Register the geometry column
select RecoverGeometryColumn('test','geometry',4326,'POINT');
Test
insert into test values (1,ST_GeomFromText('POINT (0 0)',4326));
insert into test values (2,ST_GeomFromText('POINT (0 0)',4326));
insert into test values (2,ST_GeomFromText('POINT (0 1)',4326));
First and third commands are succesful but the second one fails with a UNIQUE constraint failed error.
EDIT
The better way than to rely in plain SQLite and UNIQUE constraint would be to write a trigger that uses the SpatiaLite fucntion ST_Equal http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p12
ST_Equals returns TRUE even if the members of geometry collections are expressed in different order like here:
select ST_Equals(
ST_GeomFromText('MULTIPOINT ((0 0),(1 1))'),
ST_GeomFromText('MULTIPOINT ((1 1),(0 0))'));
As a template for such trigger you can use the trigger that SpatiaLite adds automatically to a new geometry columns for addind a SRID constraint
CREATE TRIGGER "ggi_test_geometry" BEFORE INSERT ON "test"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'test.geometry violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('test') AND Lower(f_geometry_column) = Lower('geometry')
AND GeometryConstraints(NEW."geometry", geometry_type, srid) = 1) IS NULL;
END