在这里,我创建了一个SoftwareEngineer
节点。我想确保只有一个SoftwareEngineer
可以存在于一个SocialSecurityNumber
上。如何使用Apache AGE实现此功能?
test=# SELECT * FROM cypher('staff_details', $$
CREATE(e: SoftwareEngineer {
name: 'Muneeb',
SocialSecurityNumber: '12345',
date: pg_catalog.now()
}) RETURN e
$$) as (e agtype);
当我运行上面的查询两次时,我得到了两个包含完全相同数据的节点。
test=# Select * from cypher('staff_details', $$ MATCH (v:SoftwareEngineer) RETURN v $$) as (v agtype);
给:
{"id": 1407374883553281, "label": "SoftwareEngineer", "properties": {"date": "2023-04-01T07:23:10.069163+05:00", "name"
: "Muneeb", "SocialSecurityNumber": "12345"}}::vertex
{"id": 1407374883553282, "label": "SoftwareEngineer", "properties": {"date": "2023-04-01T07:28:39.245981+05:00", "name"
: "Muneeb", "SocialSecurityNumber": "12345"}}::vertex
(2 rows)
虽然id属性不同,但数据是相同的。我希望Apache AGE限制创建两个具有相同SocialSecurityNumber
的节点,如Primary Key
。
Github上有一个关于类似问题的讨论。请查看一下。从我尝试过的,适用于你的情况是:
-
Create Function "get_ssn"它将返回属性SocialSecurityNumber从properties"列
CREATE OR REPLACE FUNCTION get_ssn(properties agtype) RETURNS agtype AS $BODY$ select agtype_access_operator($1, '"SocialSecurityNumber"'); $BODY$ LANGUAGE sql IMMUTABLE;
-
为属性"SocialSecurityNumber"创建唯一索引
CREATE UNIQUE INDEX person_ssn_idx ON staff_details."SoftwareEngineer"(get_ssn(properties)) ;
在以
方式运行上面的create INDEX查询之前,您可能需要首先创建顶点标签(如果还没有创建)。SELECT * FROM create_vlabel('staff_details', 'SoftwareEngineer');
现在,当您尝试添加具有相同SocialSecurityNumber的另一个节点时,您会得到错误:
ERROR: duplicate key value violates unique constraint "person_ssn_idx" DETAIL: Key (get_ssn(properties))=("12345") already exists.
为每个人创建一个唯一的SocialSecurityNumber,以便两个Softwareengineer节点具有相同的id(SocialSecurityNumber)
CREATE CONSTRAINT ON (n:SoftwareEngineer) ASSERT n.SocialSecurityNumber IS UNIQUE;
您可以使用MERGE实现这一点,如下所示,假设SocialSecurityNumber
是主键。
SELECT * FROM cypher('staff_details', $$
MERGE (e: SoftwareEngineer {
SocialSecurityNumber: '12345'
})
SET e.name = 'Muneeb', e.date = COALESCE(e.date, timestamp())
RETURN e
$$) as (e agtype);
SELECT * FROM staff_details."SoftwareEngineer";
引用
- 合并文档。
- 合并文档。
在Apache AGE (A Graph Extension for PostgreSQL)中,你可以通过使用约束和触发器来实现一个SocialSecurityNumber只能存在一个SoftwareEngineer节点的功能。
:您可以在SoftwareEngineer标签的SocialSecurityNumber属性上创建唯一性约束。这将强制惟一性,并防止创建具有相同SocialSecurityNumber的重复节点。下面是如何使用Cypher创建唯一性约束的示例:
CREATE CONSTRAINT ON (se:SoftwareEngineer) ASSERT se.SocialSecurityNumber IS UNIQUE;
触发您还可以使用触发器在数据库级别强制惟一性约束。可以创建一个触发器,在插入新节点之前检查具有相同SocialSecurityNumber的SoftwareEngineer节点是否已经存在。下面是一个如何使用Cypher创建触发器的示例:
CREATE TRIGGER check_unique_social_security_number
BEFORE INSERT ON SoftwareEngineer
FOR EACH ROW
BEGIN
IF EXISTS (
MATCH (se:SoftwareEngineer {SocialSecurityNumber: NEW.SocialSecurityNumber})
RETURN se
)
THEN
RAISE EXCEPTION 'A SoftwareEngineer with the same SocialSecurityNumber already exists.';
END IF;
END;
有了这些约束和触发器,如果您尝试插入具有重复SocialSecurityNumber的SoftwareEngineer节点,将导致引发异常,从而阻止重复节点的创建。
注意:提供的示例假设您已经在您的图模式中创建了SoftwareEngineer标签和相关属性。根据您的模式设计相应地调整Cypher语句。
在Apache AGE中,您可以使用触发器实现属性SocialSecurityNumber
的唯一性.首先需要创建触发器函数:
CREATE OR REPLACE FUNCTION check_unique_ssn()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM SoftwareEngineer
WHERE SocialSecurityNumber = NEW.SocialSecurityNumber
) THEN
RAISE EXCEPTION 'A SoftwareEngineer with the same SocialSecurityNumber already exists';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
然后,您需要创建触发器并将值插入其中:
CREATE TRIGGER enforce_unique_ssn
BEFORE INSERT ON SoftwareEngineer
FOR EACH ROW
EXECUTE FUNCTION check_unique_ssn();
INSERT INTO SoftwareEngineer (name, SocialSecurityNumber, date)
VALUES ('Muneeb', '12345', NOW());
SELECT * FROM cypher('staff_details', $$
MATCH (existing:SoftwareEngineer { SocialSecurityNumber: '12345' })
RETURN existing
$$) as (existing agtype);
试试这个查询!
使用这个查询:
CREATE OR REPLACE FUNCTION create_pk(properties agtype)
RETURNS agtype AS
$BODY$
SELECT agtype_access_operator($1, '"SocialSecurityNumber"');
$BODY$
LANGUAGE sql IMMUTABLE;
CREATE UNIQUE INDEX person_pk_idx ON staff_details."SoftwareEngineer"
(create_pk(properties));