Uniqueness Constraints - Ensuring Unique Property Values | Tags

<h2 id="uniqueness-constraints" class="position-relative d-flex align-items-center group"> Uniqueness Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="uniqueness-constraints" aria-haspopup="dialog" aria-label="Share link: Uniqueness Constraints"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>Uniqueness constraints ensure that property values are unique across all nodes or edges of a given type, preventing duplicates and maintaining data integrity. They enable efficient lookups, support primary key semantics, and automatically create indexes for fast unique value checking. <h3 id="understanding-uniqueness-constraints" class="position-relative d-flex align-items-center group"> Understanding Uniqueness Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="understanding-uniqueness-constraints" aria-haspopup="dialog" aria-label="Share link: Understanding Uniqueness Constraints"> Share link </button> </h3> <h4 id="what-are-uniqueness-constraints" class="position-relative d-flex align-items-center group"> What are Uniqueness Constraints? <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-are-uniqueness-constraints" aria-haspopup="dialog" aria-label="Share link: What are Uniqueness Constraints?"> Share link </button> </h4>Uniqueness constraints enforce data integrity: Duplicate Prevention - Reject INSERT/UPDATE creating duplicate values Automatic Indexing - Create unique indexes for fast lookups Primary Key Support - Identify nodes/edges uniquely Referential Integrity - Enable foreign key relationships Query Optimization - Optimizer knows values are unique <h4 id="benefits" class="position-relative d-flex align-items-center group"> Benefits <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="benefits" aria-haspopup="dialog" aria-label="Share link: Benefits"> Share link </button> </h4><ul> <li>Data Quality: Prevent accidental duplicates</li> <li>Performance: Fast unique value lookups via indexes</li> <li>Consistency: Guarantee system-wide uniqueness</li> <li>Simplicity: Declarative constraint vs application logic</li> <li>Concurrency: Database-level uniqueness checking</li> </ul> <h3 id="defining-unique-constraints" class="position-relative d-flex align-items-center group"> Defining Unique Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="defining-unique-constraints" aria-haspopup="dialog" aria-label="Share link: Defining Unique Constraints"> Share link </button> </h3> <h4 id="single-column-uniqueness" class="position-relative d-flex align-items-center group"> Single Column Uniqueness <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="single-column-uniqueness" aria-haspopup="dialog" aria-label="Share link: Single Column Uniqueness"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Property type with unique constraint CREATE PROPERTY TYPE Person ( id STRING PRIMARY KEY, email STRING UNIQUE, ssn STRING UNIQUE, username STRING UNIQUE, name STRING, age INTEGER ); -- Add unique constraint to existing type ALTER PROPERTY TYPE Person ADD CONSTRAINT unique_email UNIQUE (email); -- Named constraint ALTER PROPERTY TYPE Product ADD CONSTRAINT uk_product_sku UNIQUE (sku); </code></pre></div> <h4 id="composite-unique-constraints" class="position-relative d-flex align-items-center group"> Composite Unique Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="composite-unique-constraints" aria-haspopup="dialog" aria-label="Share link: Composite Unique Constraints"> Share link </button> </h4>Multiple columns must be unique together: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Composite uniqueness CREATE PROPERTY TYPE Booking ( id STRING PRIMARY KEY, room_id STRING, date DATE, guest_id STRING, -- Room can only be booked once per date UNIQUE (room_id, date) ); -- Multiple composite constraints CREATE PROPERTY TYPE OrderItem ( order_id STRING, product_id STRING, line_number INTEGER, UNIQUE (order_id, product_id), -- Same product only once per order UNIQUE (order_id, line_number) -- Unique line numbers per order ); -- Add composite constraint ALTER PROPERTY TYPE Enrollment ADD CONSTRAINT uk_student_course UNIQUE (student_id, course_id, semester); </code></pre></div> <h4 id="partial-unique-constraints" class="position-relative d-flex align-items-center group"> Partial Unique Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="partial-unique-constraints" aria-haspopup="dialog" aria-label="Share link: Partial Unique Constraints"> Share link </button> </h4>Uniqueness with conditions: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Unique only for active records CREATE PROPERTY TYPE User ( id STRING PRIMARY KEY, email STRING, status STRING, UNIQUE (email) WHERE status = 'active' ); -- Unique non-NULL values only CREATE PROPERTY TYPE Employee ( id STRING PRIMARY KEY, email STRING, work_email STRING, UNIQUE (work_email) WHERE work_email IS NOT NULL ); </code></pre></div> <h3 id="primary-keys" class="position-relative d-flex align-items-center group"> Primary Keys <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="primary-keys" aria-haspopup="dialog" aria-label="Share link: Primary Keys"> Share link </button> </h3> <h4 id="single-column-primary-key" class="position-relative d-flex align-items-center group"> Single Column Primary Key <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="single-column-primary-key" aria-haspopup="dialog" aria-label="Share link: Single Column Primary Key"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE PROPERTY TYPE Person ( id STRING PRIMARY KEY, name STRING, email STRING ); -- Equivalent to: CREATE PROPERTY TYPE Person ( id STRING UNIQUE NOT NULL, name STRING, email STRING ); </code></pre></div> <h4 id="composite-primary-key" class="position-relative d-flex align-items-center group"> Composite Primary Key <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="composite-primary-key" aria-haspopup="dialog" aria-label="Share link: Composite Primary Key"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Natural composite key CREATE PROPERTY TYPE OrderItem ( order_id STRING, product_id STRING, quantity INTEGER, price DECIMAL, PRIMARY KEY (order_id, product_id) ); -- Multi-column primary key CREATE PROPERTY TYPE TimeSeriesData ( sensor_id STRING, timestamp TIMESTAMP, metric_name STRING, value DECIMAL, PRIMARY KEY (sensor_id, timestamp, metric_name) ); </code></pre></div> <h4 id="auto-generated-keys" class="position-relative d-flex align-items-center group"> Auto-Generated Keys <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="auto-generated-keys" aria-haspopup="dialog" aria-label="Share link: Auto-Generated Keys"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- UUID primary key CREATE PROPERTY TYPE User ( id STRING PRIMARY KEY DEFAULT uuid(), username STRING UNIQUE, email STRING UNIQUE ); -- Auto-incrementing integer CREATE PROPERTY TYPE Product ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name STRING, sku STRING UNIQUE ); </code></pre></div> <h3 id="unique-indexes" class="position-relative d-flex align-items-center group"> Unique Indexes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="unique-indexes" aria-haspopup="dialog" aria-label="Share link: Unique Indexes"> Share link </button> </h3> <h4 id="creating-unique-indexes" class="position-relative d-flex align-items-center group"> Creating Unique Indexes <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="creating-unique-indexes" aria-haspopup="dialog" aria-label="Share link: Creating Unique Indexes"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Unique index on single column CREATE UNIQUE INDEX idx_person_email ON Person(email); -- Unique index on multiple columns CREATE UNIQUE INDEX idx_booking_room_date ON Booking(room_id, date); -- Partial unique index CREATE UNIQUE INDEX idx_active_user_email ON User(email) WHERE status = 'active'; -- Case-insensitive unique index CREATE UNIQUE INDEX idx_user_username ON User(LOWER(username)); </code></pre></div> <h4 id="index-benefits" class="position-relative d-flex align-items-center group"> Index Benefits <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-benefits" aria-haspopup="dialog" aria-label="Share link: Index Benefits"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Fast unique lookups MATCH (u:User {email: $email}) RETURN u; -- Uses unique index, O(log n) lookup -- Constraint violation check during insert CREATE (u:User {email: 'existing@example.com'}); -- Instant check via unique index, no table scan </code></pre></div> <h3 id="handling-constraint-violations" class="position-relative d-flex align-items-center group"> Handling Constraint Violations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="handling-constraint-violations" aria-haspopup="dialog" aria-label="Share link: Handling Constraint Violations"> Share link </button> </h3> <h4 id="detecting-violations" class="position-relative d-flex align-items-center group"> Detecting Violations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="detecting-violations" aria-haspopup="dialog" aria-label="Share link: Detecting Violations"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">from geode_client import Client, UniqueViolationError async def create_user(email, name): try: client = Client(host="localhost", port=3141) async with client.connection() as conn: result, _ = await conn.query(""" CREATE (u:User { email: $email, name: $name }) RETURN u.id """, {"email": email, "name": name}) return result.rows[0]['u.id'] except UniqueViolationError as e: print(f"Email {email} already exists") print(f"Constraint: {e.constraint_name}") print(f"Column: {e.column_name}") raise except Exception as e: print(f"Unexpected error: {e}") raise </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-go" data-lang="go">// Go error handling _, err := db.Exec(` CREATE (u:User {email: $1, name: $2}) `, email, name) if err != nil { if isUniqueViolation(err) { return fmt.Errorf("email %s already exists", email) } return err } func isUniqueViolation(err error) bool { var geodeErr *geode.Error if errors.As(err, &geodeErr) { return geodeErr.Code == "23505" // Unique violation SQL code } return false } </code></pre></div> <h4 id="error-messages" class="position-relative d-flex align-items-center group"> Error Messages <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="error-messages" aria-haspopup="dialog" aria-label="Share link: Error Messages"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gdscript3" data-lang="gdscript3">UniqueViolationError: duplicate key value violates unique constraint "uk_person_email" Detail: Key (email)=(alice@example.com) already exists. Constraint: uk_person_email Table: Person Column: email </code></pre></div> <h3 id="upsert-patterns" class="position-relative d-flex align-items-center group"> Upsert Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="upsert-patterns" aria-haspopup="dialog" aria-label="Share link: Upsert Patterns"> Share link </button> </h3> <h4 id="merge-statement" class="position-relative d-flex align-items-center group"> MERGE Statement <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="merge-statement" aria-haspopup="dialog" aria-label="Share link: MERGE Statement"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Insert or update based on unique key MERGE (u:User {email: $email}) ON CREATE SET u.name = $name, u.created_at = NOW(), u.status = 'active' ON MATCH SET u.name = $name, u.updated_at = NOW(); -- Composite key merge MERGE (b:Booking {room_id: $room_id, date: $date}) ON CREATE SET b.guest_id = $guest_id, b.status = 'confirmed', b.created_at = NOW() ON MATCH SET b.status = 'modified', b.updated_at = NOW(); </code></pre></div> <h4 id="insert--on-conflict" class="position-relative d-flex align-items-center group"> INSERT &hellip; ON CONFLICT <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="insert--on-conflict" aria-haspopup="dialog" aria-label="Share link: INSERT ON CONFLICT"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- PostgreSQL-style upsert INSERT INTO Person (id, email, name) VALUES ($id, $email, $name) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW(); -- Do nothing on conflict INSERT INTO Product (sku, name, price) VALUES ($sku, $name, $price) ON CONFLICT (sku) DO NOTHING; </code></pre></div> <h4 id="application-level-upsert" class="position-relative d-flex align-items-center group"> Application-Level Upsert <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="application-level-upsert" aria-haspopup="dialog" aria-label="Share link: Application-Level Upsert"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">async def upsert_user(email, name): client = Client(host="localhost", port=3141) async with client.connection() as conn: # Try insert first try: result, _ = await conn.query(""" CREATE (u:User {email: $email, name: $name}) RETURN u.id AS id, 'created' AS action """, {"email": email, "name": name}) return result.rows[0] except UniqueViolationError: # Update existing result, _ = await conn.query(""" MATCH (u:User {email: $email}) SET u.name = $name, u.updated_at = NOW() RETURN u.id AS id, 'updated' AS action """, {"email": email, "name": name}) return result.rows[0] </code></pre></div> <h3 id="managing-constraints" class="position-relative d-flex align-items-center group"> Managing Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="managing-constraints" aria-haspopup="dialog" aria-label="Share link: Managing Constraints"> Share link </button> </h3> <h4 id="listing-constraints" class="position-relative d-flex align-items-center group"> Listing Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="listing-constraints" aria-haspopup="dialog" aria-label="Share link: Listing Constraints"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Show all constraints SELECT constraint_name, table_name, column_names, constraint_type FROM system.constraints WHERE constraint_type = 'UNIQUE' ORDER BY table_name, constraint_name; -- Constraints for specific table SELECT * FROM system.constraints WHERE table_name = 'Person' AND constraint_type IN ('UNIQUE', 'PRIMARY KEY'); </code></pre></div> <h4 id="dropping-constraints" class="position-relative d-flex align-items-center group"> Dropping Constraints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="dropping-constraints" aria-haspopup="dialog" aria-label="Share link: Dropping Constraints"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Drop unique constraint ALTER PROPERTY TYPE Person DROP CONSTRAINT unique_email; -- Drop primary key ALTER PROPERTY TYPE Product DROP PRIMARY KEY; -- Drop named constraint ALTER PROPERTY TYPE Booking DROP CONSTRAINT uk_room_date; </code></pre></div> <h4 id="temporarily-disabling" class="position-relative d-flex align-items-center group"> Temporarily Disabling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="temporarily-disabling" aria-haspopup="dialog" aria-label="Share link: Temporarily Disabling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Disable for bulk import ALTER PROPERTY TYPE Person DISABLE CONSTRAINT unique_email; -- Bulk import LOAD CSV FROM 'users.csv' ... -- Re-enable and validate ALTER PROPERTY TYPE Person ENABLE CONSTRAINT unique_email; </code></pre></div> <h3 id="performance-considerations" class="position-relative d-flex align-items-center group"> Performance Considerations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-considerations" aria-haspopup="dialog" aria-label="Share link: Performance Considerations"> Share link </button> </h3> <h4 id="index-selectivity" class="position-relative d-flex align-items-center group"> Index Selectivity <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-selectivity" aria-haspopup="dialog" aria-label="Share link: Index Selectivity"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- High selectivity (good for unique index) CREATE UNIQUE INDEX idx_user_email ON User(email); -- Each email is unique, perfect for index -- Low selectivity (bad for unique index) CREATE INDEX idx_user_status ON User(status); -- Status has few values, not unique </code></pre></div> <h4 id="constraint-checking-cost" class="position-relative d-flex align-items-center group"> Constraint Checking Cost <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="constraint-checking-cost" aria-haspopup="dialog" aria-label="Share link: Constraint Checking Cost"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Unique constraint check happens on every insert/update # Cost: O(log n) with unique index # Without unique constraint, application must query: result, _ = await client.query( "MATCH (u:User {email: $email}) RETURN COUNT(u)", {"email": email} ) if result.rows[0]['COUNT(u)'] > 0: raise ValueError("Email already exists") # Cost: O(log n) + application round-trip # Less efficient than database constraint </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="constraint-design" class="position-relative d-flex align-items-center group"> Constraint Design <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="constraint-design" aria-haspopup="dialog" aria-label="Share link: Constraint Design"> Share link </button> </h4><ol> <li>Use PRIMARY KEY for Identity: Natural or surrogate key</li> <li>UNIQUE for Natural Keys: Email, username, SSN, etc.</li> <li>Composite for Relationships: (user_id, friend_id) pairs</li> <li>Name Constraints: Explicit names for better error messages</li> <li>Add Indexes: Unique constraints automatically indexed</li> </ol> <h4 id="application-patterns" class="position-relative d-flex align-items-center group"> Application Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="application-patterns" aria-haspopup="dialog" aria-label="Share link: Application Patterns"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Good: Let database enforce uniqueness async def create_user(email, name): try: result, _ = await client.query( "CREATE (u:User {email: $email, name: $name})", {"email": email, "name": name} ) return result except UniqueViolationError: return None # Handle duplicate # Bad: Check-then-insert (race condition) async def create_user_bad(email, name): # Race condition between check and insert! existing, _ = await client.query( "MATCH (u:User {email: $email}) RETURN u", {"email": email} ) if existing: return None result, _ = await client.query( "CREATE (u:User {email: $email, name: $name})", {"email": email, "name": name} ) return result </code></pre></div> <h4 id="error-handling" class="position-relative d-flex align-items-center group"> Error Handling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="error-handling" aria-haspopup="dialog" aria-label="Share link: Error Handling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Provide user-friendly messages try: await create_user(email, name) except UniqueViolationError as e: if e.column_name == 'email': raise ValueError(f"Email {email} is already registered") elif e.column_name == 'username': raise ValueError(f"Username is already taken") else: raise ValueError("Duplicate value detected") </code></pre></div> <h3 id="troubleshooting" class="position-relative d-flex align-items-center group"> Troubleshooting <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="troubleshooting" aria-haspopup="dialog" aria-label="Share link: Troubleshooting"> Share link </button> </h3> <h4 id="finding-duplicates" class="position-relative d-flex align-items-center group"> Finding Duplicates <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="finding-duplicates" aria-haspopup="dialog" aria-label="Share link: Finding Duplicates"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find duplicate emails before adding constraint SELECT email, COUNT(*) AS count FROM Person GROUP BY email HAVING COUNT(*) > 1; -- Find all duplicates with details SELECT p1.id, p1.email, p1.name FROM Person p1 JOIN Person p2 ON p1.email = p2.email WHERE p1.id < p2.id ORDER BY p1.email; </code></pre></div> <h4 id="resolving-duplicates" class="position-relative d-flex align-items-center group"> Resolving Duplicates <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="resolving-duplicates" aria-haspopup="dialog" aria-label="Share link: Resolving Duplicates"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Keep first, delete duplicates DELETE FROM Person p1 WHERE EXISTS ( SELECT 1 FROM Person p2 WHERE p1.email = p2.email AND p2.id < p1.id ); -- Merge duplicates MATCH (p1:Person), (p2:Person) WHERE p1.email = p2.email AND p1.id < p2.id // Merge logic... DELETE p2; </code></pre></div> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/constraints/" >Constraints</a> - All constraint types</li> <li><a href="/tags/indexes/" >Indexes</a> - Index optimization</li> <li><a href="/tags/data-integrity/" >Data Integrity</a> - Data quality</li> </ul> <h3 id="further-reading" class="position-relative d-flex align-items-center group"> Further Reading <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="further-reading" aria-haspopup="dialog" aria-label="Share link: Further Reading"> Share link </button> </h3><ul> <li><a href="https://www.postgresql.org/docs/current/ddl-constraints.html" aria-label="PostgreSQL Unique Constraints – opens in new window" target="_blank" rel="noopener noreferrer" >PostgreSQL Unique Constraints ↗ </a> - PostgreSQL documentation</li> <li><a href="https://en.wikipedia.org/wiki/Unique_key" aria-label="Database Constraints – opens in new window" target="_blank" rel="noopener noreferrer" >Database Constraints ↗ </a> - Wikipedia overview</li> </ul>

Popular

Uniqueness Constraints - Ensuring Unique Property Values

Related Articles