{"Entry":{"collection":"sql","key":"alter-domain","name":"ALTER DOMAIN","aliases":["alterdomain"],"metadata":{"aliases":["alterdomain"],"changed_in":["8.1","9.2","9.5","12","14"],"changes":[{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description","parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["ALTER DOMAIN name","SET SCHEMA new_schema"],"removed":[]},"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":[],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["ADD domain_constraint [ NOT VALID ]","DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]","RENAME CONSTRAINT constraint_name TO new_constraint_name","ALTER DOMAIN name","VALIDATE CONSTRAINT constraint_name","ALTER DOMAIN name","RENAME TO new_name","ALTER DOMAIN name"],"removed":[]},"to":"9.2"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":["OWNER TO { new_owner | CURRENT_USER | SESSION_USER }"],"removed":[]},"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ CONSTRAINT constraint_name ]","{ NOT NULL | CHECK (expression) }"],"removed":[]},"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":["OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }"],"removed":[]},"to":"14"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"de304b18036dab0490e62f59b61f2ff9b86e316ecc4b9abaa29951e126ecbad5","editorial":{},"first_version":"7.4","group":"type","imported_at":"2026-09-30T17:43:37.469266+08:00","last_version":"20","name":"ALTER DOMAIN","object":"DOMAIN","position":3009,"present_in":["7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"change the definition of a domain","purpose_zh":"","related":["create-domain","drop-domain"],"slug":"alter-domain","source_rev":"a709ab85","synopsis":"ALTER DOMAIN name\n{ SET DEFAULT expression | DROP DEFAULT }\nALTER DOMAIN name\n{ SET | DROP } NOT NULL\nALTER DOMAIN name\nADD domain_constraint [ NOT VALID ]\nALTER DOMAIN name\nDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\nALTER DOMAIN name\nRENAME CONSTRAINT constraint_name TO new_constraint_name\nALTER DOMAIN name\nVALIDATE CONSTRAINT constraint_name\nALTER DOMAIN name\nOWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER DOMAIN name\nRENAME TO new_name\nALTER DOMAIN name\nSET SCHEMA new_schema\nwhere domain_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | CHECK (expression) }","verb":"ALTER"}},"Definition":{"Collection":"sql","Key":"alter-domain","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"alter-domain","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-ALTERDOMAIN","file":"sql-alterdomain.html","lang":"en","name":"ALTER DOMAIN","purpose":"change the definition of a domain","purpose_zh":"","related":["create-domain","drop-domain"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e changes the definition of an existing domain. There are several sub-forms:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e/\u003ccode class=\"literal\"\u003eDROP DEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThese forms set or remove the default value for a domain. Note that defaults only apply to subsequent \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e commands; they do not affect rows already in a table using the domain.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e/\u003ccode class=\"literal\"\u003eDROP NOT NULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThese forms change whether a domain is marked to allow NULL values or to reject NULL values. You can only \u003ccode class=\"literal\"\u003eSET NOT NULL\u003c/code\u003e when the columns using the domain contain no null values.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eADD \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ NOT VALID ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form adds a new constraint to a domain. When a new constraint is added to a domain, all columns using that domain will be checked against the newly added constraint. These checks can be suppressed by adding the new constraint using the \u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e option; the constraint can later be made valid using \u003ccode class=\"command\"\u003eALTER DOMAIN ... VALIDATE CONSTRAINT\u003c/code\u003e. Newly inserted or updated rows are always checked against all constraints, even those marked \u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e. \u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e is only accepted for \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e constraints.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDROP CONSTRAINT [ IF EXISTS ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form drops constraints on a domain. If \u003ccode class=\"literal\"\u003eIF EXISTS\u003c/code\u003e is specified and the constraint does not exist, no error is thrown. In this case a notice is issued instead.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRENAME CONSTRAINT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form changes the name of a constraint on a domain.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVALIDATE CONSTRAINT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form validates a constraint previously added as \u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e, that is, it verifies that all values in table columns of the domain type satisfy the specified constraint.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOWNER\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form changes the owner of the domain to the specified user.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRENAME\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form changes the name of the domain.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET SCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis form changes the schema of the domain. Any constraints associated with the domain are moved into the new schema as well.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eYou must own the domain to use \u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e. To change the schema of a domain, you must also have \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the new schema. To alter the owner, you must be able to \u003ccode class=\"literal\"\u003eSET ROLE\u003c/code\u003e to the new owning role, and that role must have \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the domain's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the domain. However, a superuser can alter ownership of any domain anyway.)\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (possibly schema-qualified) of an existing domain to alter.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eNew domain constraint for the domain.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eName of an existing constraint to drop or rename.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDo not verify existing stored data for constraint validity.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCASCADE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAutomatically drop objects that depend on the constraint, and in turn all objects that depend on those objects (see \u003ca href=\"/docs/18/ddl-depend.html\" title=\"5.15. Dependency Tracking\"\u003eSection 5.15\u003c/a\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRESTRICT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eRefuse to drop the constraint if there are any dependent objects. This is the default behavior.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe new name for the domain.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_constraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe new name for the constraint.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe user name of the new owner of the domain.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_schema\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe new schema for the domain.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eAlthough \u003ccode class=\"command\"\u003eALTER DOMAIN ADD CONSTRAINT\u003c/code\u003e attempts to verify that existing stored data satisfies the new constraint, this check is not bulletproof, because the command cannot \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003esee\u003c/span\u003e”\u003c/span\u003e table rows that are newly inserted or updated and not yet committed. If there is a hazard that concurrent operations might insert bad data, the way to proceed is to add the constraint using the \u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e option, commit that command, wait until all transactions started before that commit have finished, and then issue \u003ccode class=\"command\"\u003eALTER DOMAIN VALIDATE CONSTRAINT\u003c/code\u003e to search for data violating the constraint. This method is reliable because once the constraint is committed, all new transactions are guaranteed to enforce it against new values of the domain type.\u003c/p\u003e\u003cp\u003eCurrently, \u003ccode class=\"command\"\u003eALTER DOMAIN ADD CONSTRAINT\u003c/code\u003e, \u003ccode class=\"command\"\u003eALTER DOMAIN VALIDATE CONSTRAINT\u003c/code\u003e, and \u003ccode class=\"command\"\u003eALTER DOMAIN SET NOT NULL\u003c/code\u003e will fail if the named domain or any derived domain is used within a container-type column (a composite, array, or range column) in any table in the database. They should eventually be improved to be able to verify the new constraint for such nested values.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eTo add a \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e constraint to a domain:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode SET NOT NULL;\n\u003c/pre\u003e\u003cp\u003eTo remove a \u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e constraint from a domain:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode DROP NOT NULL;\n\u003c/pre\u003e\u003cp\u003eTo add a check constraint to a domain:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);\n\u003c/pre\u003e\u003cp\u003eTo remove a check constraint from a domain:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode DROP CONSTRAINT zipchk;\n\u003c/pre\u003e\u003cp\u003eTo rename a check constraint on a domain:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;\n\u003c/pre\u003e\u003cp\u003eTo move the domain into a different schema:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode SET SCHEMA customers;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard, except for the \u003ccode class=\"literal\"\u003eOWNER\u003c/code\u003e, \u003ccode class=\"literal\"\u003eRENAME\u003c/code\u003e, \u003ccode class=\"literal\"\u003eSET SCHEMA\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eVALIDATE CONSTRAINT\u003c/code\u003e variants, which are \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extensions. The \u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e clause of the \u003ccode class=\"literal\"\u003eADD CONSTRAINT\u003c/code\u003e variant is also a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-domain/?v=18\" title=\"CREATE DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE DOMAIN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-domain/?v=18\" title=\"DROP DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP DOMAIN\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"ALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    { SET DEFAULT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DROP DEFAULT }\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    { SET | DROP } NOT NULL\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    ADD \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ NOT VALID ]\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    DROP CONSTRAINT [ IF EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e [ RESTRICT | CASCADE ]\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n     RENAME CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_constraint_name\u003c/code\u003e\u003c/em\u003e\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    VALIDATE CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    OWNER TO { \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    RENAME TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    SET SCHEMA \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_schema\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e is:\u003c/span\u003e\n\n[ CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e ]\n{ NOT NULL | CHECK (\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e) }","synopsis_text":"ALTER DOMAIN name\n{ SET DEFAULT expression | DROP DEFAULT }\nALTER DOMAIN name\n{ SET | DROP } NOT NULL\nALTER DOMAIN name\nADD domain_constraint [ NOT VALID ]\nALTER DOMAIN name\nDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\nALTER DOMAIN name\nRENAME CONSTRAINT constraint_name TO new_constraint_name\nALTER DOMAIN name\nVALIDATE CONSTRAINT constraint_name\nALTER DOMAIN name\nOWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER DOMAIN name\nRENAME TO new_name\nALTER DOMAIN name\nSET SCHEMA new_schema\nwhere domain_constraint is:\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | CHECK (expression) }"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"alter-domain","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"ALTER DOMAIN","Summary":"更改一个域的定义","BodyHTML":"\u003cpre\u003eALTER DOMAIN name\n{ SET DEFAULT expression | DROP DEFAULT }\nALTER DOMAIN name\n{ SET | DROP } NOT NULL\nALTER DOMAIN name\nADD domain_constraint [ NOT VALID ]\nALTER DOMAIN name\nDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\nALTER DOMAIN name\nRENAME CONSTRAINT constraint_name TO new_constraint_name\nALTER DOMAIN name\nVALIDATE CONSTRAINT constraint_name\nALTER DOMAIN name\nOWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER DOMAIN name\nRENAME TO new_name\nALTER DOMAIN name\nSET SCHEMA new_schema\n其中 domain_constraint 是：\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | CHECK (expression) }\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eALTER DOMAIN\u003c/code\u003e更改一个现有域的定义。有以下几种子形式：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSET\u003c/code\u003e/\u003ccode\u003eDROP DEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这些形式为域设置或移除默认值。注意，默认值只适用于后续的 \u003ccode\u003eINSERT\u003c/code\u003e命令；它们不会影响已存在于使用该域的表中的行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSET\u003c/code\u003e/\u003ccode\u003eDROP NOT NULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这些形式更改域是被标记为允许 NULL 值还是拒绝 NULL 值。只有当使用该域的列中不包含空值时，才能执行\u003ccode\u003eSET NOT NULL\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eADD \u003cem\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ NOT VALID ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式向域中添加一个新约束。当向域添加新约束时，所有使用该域的列都会根据新添加的约束进行检查。可以通过使用\u003ccode\u003eNOT VALID\u003c/code\u003e选项来添加新约束，从而抑制这些检查；之后可以使用\u003ccode\u003eALTER DOMAIN ... VALIDATE CONSTRAINT\u003c/code\u003e 验证该约束。新插入或更新的行始终都会根据所有约束进行检查，即使这些约束被标记为\u003ccode\u003eNOT VALID\u003c/code\u003e。\u003ccode\u003eNOT VALID\u003c/code\u003e选项仅适用于 \u003ccode\u003eCHECK\u003c/code\u003e约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDROP CONSTRAINT [ IF EXISTS ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式删除域上的约束。如果指定了\u003ccode\u003eIF EXISTS\u003c/code\u003e而该约束不存在，则不会抛出错误，而是发出一条提示。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eRENAME CONSTRAINT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式更改域上某个约束的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eVALIDATE CONSTRAINT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式验证一个先前以\u003ccode\u003eNOT VALID\u003c/code\u003e方式添加的约束，也就是说，它会核实该域类型的表列中的所有值都满足指定约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eOWNER\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式将域的拥有者更改为指定用户。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eRENAME\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式更改域的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSET SCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式更改域所在的模式。与该域关联的任何约束也会一并移动到新模式中。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e要使用\u003ccode\u003eALTER DOMAIN\u003c/code\u003e，你必须拥有该域。要更改域的模式，你还必须在新模式上具有\u003ccode\u003eCREATE\u003c/code\u003e权限。要更改拥有者，你必须能够对新的拥有角色执行\u003ccode\u003eSET ROLE\u003c/code\u003e，并且该角色必须在该域所在的模式上具有 \u003ccode\u003eCREATE\u003c/code\u003e权限。（这些限制确保更改拥有者不会做出任何你不能通过删除并重新创建该域来完成的事情。不过，超级用户无论如何都可以更改任何域的所有权。）\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要修改的现有域的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的新约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要删除或重命名的现有约束名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eNOT VALID\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e不验证现有存储的数据是否满足该约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCASCADE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e自动删除依赖于该约束的对象，以及进一步依赖于这些对象的所有对象（见\u003ca href=\"/docs/18/ddl-depend.html\" rel=\"nofollow\"\u003e第 5.15 节\u003c/a\u003e）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eRESTRICT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果存在任何依赖对象，则拒绝删除该约束。这是默认行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e域的新名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_constraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e约束的新名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e域的新拥有者的用户名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_schema\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e域的新模式。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e尽管\u003ccode\u003eALTER DOMAIN ADD CONSTRAINT\u003c/code\u003e会尝试验证现有存储的数据是否满足新约束，但这种检查并非万无一失，因为该命令无法\u003cspan\u003e“\u003cspan\u003e看到\u003c/span\u003e”\u003c/span\u003e那些新插入或已更新但尚未提交的表行。如果存在并发操作可能插入不符合约束的数据的风险，正确做法是使用\u003ccode\u003eNOT VALID\u003c/code\u003e选项添加该约束，提交该命令，等待所有在此次提交之前启动的事务结束，然后发出\u003ccode\u003eALTER DOMAIN VALIDATE CONSTRAINT\u003c/code\u003e来查找违反该约束的数据。这种方法是可靠的，因为一旦该约束被提交，所有新事务都保证会对域类型的新值强制执行该约束。\u003c/p\u003e\u003cp\u003e目前，如果指定的域或其任何派生域被数据库中任意表的容器类型列（即复合类型列、数组列或范围列）所使用，那么\u003ccode\u003eALTER DOMAIN ADD CONSTRAINT\u003c/code\u003e、\u003ccode\u003eALTER DOMAIN VALIDATE CONSTRAINT\u003c/code\u003e和 \u003ccode\u003eALTER DOMAIN SET NOT NULL\u003c/code\u003e都会失败。将来它们应当会被改进，以便能够验证这类嵌套值上的新约束。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e向域添加一个\u003ccode\u003eNOT NULL\u003c/code\u003e约束：\u003c/p\u003e\u003cpre\u003eALTER DOMAIN zipcode SET NOT NULL;\n\u003c/pre\u003e\u003cp\u003e从域中移除一个\u003ccode\u003eNOT NULL\u003c/code\u003e约束：\u003c/p\u003e\u003cpre\u003eALTER DOMAIN zipcode DROP NOT NULL;\n\u003c/pre\u003e\u003cp\u003e向域添加一个检查约束：\u003c/p\u003e\u003cpre\u003eALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);\n\u003c/pre\u003e\u003cp\u003e从域中移除一个检查约束：\u003c/p\u003e\u003cpre\u003eALTER DOMAIN zipcode DROP CONSTRAINT zipchk;\n\u003c/pre\u003e\u003cp\u003e重命名域上的一个检查约束：\u003c/p\u003e\u003cpre\u003eALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;\n\u003c/pre\u003e\u003cp\u003e将域移动到另一个模式中：\u003c/p\u003e\u003cpre\u003eALTER DOMAIN zipcode SET SCHEMA customers;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eALTER DOMAIN\u003c/code\u003e符合\u003cacronym\u003eSQL\u003c/acronym\u003e标准，但 \u003ccode\u003eOWNER\u003c/code\u003e、\u003ccode\u003eRENAME\u003c/code\u003e、\u003ccode\u003eSET SCHEMA\u003c/code\u003e和\u003ccode\u003eVALIDATE CONSTRAINT\u003c/code\u003e 这些变体是\u003cspan\u003ePostgreSQL\u003c/span\u003e扩展；\u003ccode\u003eADD CONSTRAINT\u003c/code\u003e变体中的\u003ccode\u003eNOT VALID\u003c/code\u003e子句也是\u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/create-domain/?v=18\" title=\"CREATE DOMAIN\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE DOMAIN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-domain/?v=18\" title=\"DROP DOMAIN\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP DOMAIN\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"a05b5f84c5bcb1836465c5e18dea7cc9d4131895d6e644f76c9e05f6541c6522","Payload":{"purpose_zh":"更改一个域的定义","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e更改一个现有域的定义。有以下几种子形式：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e/\u003ccode class=\"literal\"\u003eDROP DEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这些形式为域设置或移除默认值。注意，默认值只适用于后续的 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e命令；它们不会影响已存在于使用该域的表中的行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e/\u003ccode class=\"literal\"\u003eDROP NOT NULL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这些形式更改域是被标记为允许 NULL 值还是拒绝 NULL 值。只有当使用该域的列中不包含空值时，才能执行\u003ccode class=\"literal\"\u003eSET NOT NULL\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eADD \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ NOT VALID ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式向域中添加一个新约束。当向域添加新约束时，所有使用该域的列都会根据新添加的约束进行检查。可以通过使用\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e选项来添加新约束，从而抑制这些检查；之后可以使用\u003ccode class=\"command\"\u003eALTER DOMAIN ... VALIDATE CONSTRAINT\u003c/code\u003e 验证该约束。新插入或更新的行始终都会根据所有约束进行检查，即使这些约束被标记为\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e。\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e选项仅适用于 \u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDROP CONSTRAINT [ IF EXISTS ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式删除域上的约束。如果指定了\u003ccode class=\"literal\"\u003eIF EXISTS\u003c/code\u003e而该约束不存在，则不会抛出错误，而是发出一条提示。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRENAME CONSTRAINT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式更改域上某个约束的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVALIDATE CONSTRAINT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式验证一个先前以\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e方式添加的约束，也就是说，它会核实该域类型的表列中的所有值都满足指定约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOWNER\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式将域的拥有者更改为指定用户。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRENAME\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式更改域的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET SCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这种形式更改域所在的模式。与该域关联的任何约束也会一并移动到新模式中。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e要使用\u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e，你必须拥有该域。要更改域的模式，你还必须在新模式上具有\u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。要更改拥有者，你必须能够对新的拥有角色执行\u003ccode class=\"literal\"\u003eSET ROLE\u003c/code\u003e，并且该角色必须在该域所在的模式上具有 \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。（这些限制确保更改拥有者不会做出任何你不能通过删除并重新创建该域来完成的事情。不过，超级用户无论如何都可以更改任何域的所有权。）\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要修改的现有域的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该域的新约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要删除或重命名的现有约束名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e不验证现有存储的数据是否满足该约束。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCASCADE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e自动删除依赖于该约束的对象，以及进一步依赖于这些对象的所有对象（见\u003ca href=\"/docs/18/ddl-depend.html\" title=\"5.15. 依赖跟踪\"\u003e第 5.15 节\u003c/a\u003e）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRESTRICT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果存在任何依赖对象，则拒绝删除该约束。这是默认行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e域的新名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_constraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e约束的新名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e域的新拥有者的用户名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_schema\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e域的新模式。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e尽管\u003ccode class=\"command\"\u003eALTER DOMAIN ADD CONSTRAINT\u003c/code\u003e会尝试验证现有存储的数据是否满足新约束，但这种检查并非万无一失，因为该命令无法\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e看到\u003c/span\u003e”\u003c/span\u003e那些新插入或已更新但尚未提交的表行。如果存在并发操作可能插入不符合约束的数据的风险，正确做法是使用\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e选项添加该约束，提交该命令，等待所有在此次提交之前启动的事务结束，然后发出\u003ccode class=\"command\"\u003eALTER DOMAIN VALIDATE CONSTRAINT\u003c/code\u003e来查找违反该约束的数据。这种方法是可靠的，因为一旦该约束被提交，所有新事务都保证会对域类型的新值强制执行该约束。\u003c/p\u003e\u003cp\u003e目前，如果指定的域或其任何派生域被数据库中任意表的容器类型列（即复合类型列、数组列或范围列）所使用，那么\u003ccode class=\"command\"\u003eALTER DOMAIN ADD CONSTRAINT\u003c/code\u003e、\u003ccode class=\"command\"\u003eALTER DOMAIN VALIDATE CONSTRAINT\u003c/code\u003e和 \u003ccode class=\"command\"\u003eALTER DOMAIN SET NOT NULL\u003c/code\u003e都会失败。将来它们应当会被改进，以便能够验证这类嵌套值上的新约束。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e向域添加一个\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e约束：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode SET NOT NULL;\n\u003c/pre\u003e\u003cp\u003e从域中移除一个\u003ccode class=\"literal\"\u003eNOT NULL\u003c/code\u003e约束：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode DROP NOT NULL;\n\u003c/pre\u003e\u003cp\u003e向域添加一个检查约束：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);\n\u003c/pre\u003e\u003cp\u003e从域中移除一个检查约束：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode DROP CONSTRAINT zipchk;\n\u003c/pre\u003e\u003cp\u003e重命名域上的一个检查约束：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;\n\u003c/pre\u003e\u003cp\u003e将域移动到另一个模式中：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER DOMAIN zipcode SET SCHEMA customers;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER DOMAIN\u003c/code\u003e符合\u003cacronym\u003eSQL\u003c/acronym\u003e标准，但 \u003ccode class=\"literal\"\u003eOWNER\u003c/code\u003e、\u003ccode class=\"literal\"\u003eRENAME\u003c/code\u003e、\u003ccode class=\"literal\"\u003eSET SCHEMA\u003c/code\u003e和\u003ccode class=\"literal\"\u003eVALIDATE CONSTRAINT\u003c/code\u003e 这些变体是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展；\u003ccode class=\"literal\"\u003eADD CONSTRAINT\u003c/code\u003e变体中的\u003ccode class=\"literal\"\u003eNOT VALID\u003c/code\u003e子句也是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-domain/?v=18\" title=\"CREATE DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE DOMAIN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-domain/?v=18\" title=\"DROP DOMAIN\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP DOMAIN\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"ALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    { SET DEFAULT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DROP DEFAULT }\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    { SET | DROP } NOT NULL\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    ADD \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e [ NOT VALID ]\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    DROP CONSTRAINT [ IF EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e [ RESTRICT | CASCADE ]\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n     RENAME CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_constraint_name\u003c/code\u003e\u003c/em\u003e\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    VALIDATE CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    OWNER TO { \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    RENAME TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\nALTER DOMAIN \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    SET SCHEMA \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_schema\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003edomain_constraint\u003c/code\u003e\u003c/em\u003e 是：\u003c/span\u003e\n\n[ CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e ]\n{ NOT NULL | CHECK (\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e) }","synopsis_text":"ALTER DOMAIN name\n{ SET DEFAULT expression | DROP DEFAULT }\nALTER DOMAIN name\n{ SET | DROP } NOT NULL\nALTER DOMAIN name\nADD domain_constraint [ NOT VALID ]\nALTER DOMAIN name\nDROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]\nALTER DOMAIN name\nRENAME CONSTRAINT constraint_name TO new_constraint_name\nALTER DOMAIN name\nVALIDATE CONSTRAINT constraint_name\nALTER DOMAIN name\nOWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER DOMAIN name\nRENAME TO new_name\nALTER DOMAIN name\nSET SCHEMA new_schema\n其中 domain_constraint 是：\n\n[ CONSTRAINT constraint_name ]\n{ NOT NULL | CHECK (expression) }"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}