Date: Sat, 03 Sep 2005 13:50:02 -0500
From: "Robert D. Kennedy" <kennedy@fnal.gov>
Subject: Table Constraint CHECK(SELECT()) alternative?
To: pgsql-sql@postgresql.org
Cc: rdkennedy@acm.org
Reply-To: rdkennedy@acm.org
Message-id: <4319F05A.30300@fnal.gov>
MIME-version: 1.0
Content-type: multipart/signed;
	boundary=------------ms070808090907050103050607; micalg=sha1;
	protocol="application/x-pkcs7-signature"
User-Agent: Mozilla Thunderbird 1.0.6-1.1.fc3 (X11/20050720)

This is a cryptographically signed message in MIME format.

--------------ms070808090907050103050607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

	I have seen in another thread that sub-queries in a CHECK constraint have implementation ramifications that make them awkward to implement and support. OK, fair enough, c'est la vie.

ERROR:  cannot use subquery in check constraint

is the result. I have a model which seems to BEG for just such a feature though. What alternatives are there to the use of CHECK(SELECT()) in this model?

	I am implementing a basic tree of nodes. There are leafs and directories... node types. Each node in a directory has a unique name, and the node type ids point to a table with node type names. Each node points to a valid node (its parent). There is more... but now to my question. I wish to constrain parent nodes to only be directories type nodes. Leafs cannot be parents.

                                        Table "public.raw_pnfs_nodes"
     Column     |         Type          |                              Modifiers
----------------+-----------------------+---------------------------------------------------------------------
 node_id        | integer               | not null default nextval('public.raw_pnfs_nodes_node_id_seq'::text)
 parent_node_id | integer               | not null
 node_name      | character varying(80) | not null
 node_type_id   | smallint              | not null
Indexes:
    "raw_pnfs_nodes_pkey" primary key, btree (node_id)
    "raw_pnfs_nodes_node_name_key" unique, btree (node_name, parent_node_id)
Foreign-key constraints:
    "parent_refersto_node" FOREIGN KEY (parent_node_id) REFERENCES raw_pnfs_nodes(node_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    "raw_pnfs_nodes" FOREIGN KEY (node_type_id) REFERENCES node_types(node_type_id) ON UPDATE RESTRICT ON DELETE RESTRICT


What I WANT to write is the table constraint:

  CONSTRAINT parents_are_dirs_check CHECK( (SELECT A.node_type_id FROM raw_pnfs_nodes A, raw_pnfs_nodes B WHERE A.node_id = B.parent_node_id) = 1)

where "1" is the id for directory node types (ok, this is not polished yet) Hopefully this illustrates the idea. The node type of a node that is a parent to another node must be the directory node type. Is there another way to express this in SQL? I would like to avoid putting this into the business logic layer.

Thanks much,
RDK


--------------ms070808090907050103050607
Content-Type: application/x-pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIILWjCC
A6swggKToAMCAQICAgw4MA0GCSqGSIb3DQEBBQUAMGkxEzARBgoJkiaJk/IsZAEZFgNvcmcx
GDAWBgoJkiaJk/IsZAEZFghET0VHcmlkczEgMB4GA1UECxMXQ2VydGlmaWNhdGUgQXV0aG9y
aXRpZXMxFjAUBgNVBAMTDURPRUdyaWRzIENBIDEwHhcNMDQxMTA5MTkzMjM4WhcNMDUxMTA5
MTkzMjM4WjBjMRMwEQYKCZImiZPyLGQBGRYDb3JnMRgwFgYKCZImiZPyLGQBGRYIZG9lZ3Jp
ZHMxDzANBgNVBAsTBlBlb3BsZTEhMB8GA1UEAxMYUm9iZXJ0IEQuIEtlbm5lZHkgNjExMDE1
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA47sb7E9lE8LirSyUnNP3ZHe3so6j
aEL2s0INQiDa99MffwQ7GPRcqdWl6KpWmibTn0cT1SIp1SPYBpUlLSMLuTjvmswCHVuZak9s
p/MUpY6xLVSbwTo72lCQwwBU+BITL9u55S+xZOZ33y1a3Zh6QTul0DpcavHQCt2T7eeMzb90
l+AldxmWWvxftukYte3tkKYS31YdwxlRHgJQuqj5aM1g0qV+oPxFgAdb2/Y57FV2nSIlIhR/
uljRTg3s9Gft3LTWM5ogI2WgRrLLfBPTcM2oqXULty0jbczCMl4W6DS/nnCuy+tU2Zm8fvp1
WCyvz70RIrox6BGf1fUvn2msUwIDAQABo2MwYTARBglghkgBhvhCAQEEBAMCBeAwDgYDVR0P
AQH/BAQDAgTwMB8GA1UdIwQYMBaAFMoZHRKObqQ4XULUMQ4I29mNFw1dMBsGA1UdEQQUMBKB
EGtlbm5lZHlAZm5hbC5nb3YwDQYJKoZIhvcNAQEFBQADggEBAAg46jGx89tRdvj6+2mOdDED
vvd6mGEAAtLA0Q3IMYYQ/uFBhrtqYQoTpv41HRN5TRrDBIKxwcpb2GLFArLWlesV4QxM56U+
lMwcaFXo1EOUYl7jDK0CsDyvWH6X3rOxdhYnnHDTvL9R0VszpIT3IHbG2Jy1tnAdoI1o4UgT
IH0AXhY4WT1d+8w/WkipAL5Li9kvqrYaWxJDfhe5y6NlDSfKfaRLEIIOFrWlmID48R3+CQhT
0iJhNGyrVVMk+bzue6wW7iSEX4xrFjLEsBQh//RbB86um/bu1t4QpbNVPAwF1ODoHsSXUlfE
qSQ0N2ZPsHg+JNTS5CcewX/TdaPdLiMwggOrMIICk6ADAgECAgIMODANBgkqhkiG9w0BAQUF
ADBpMRMwEQYKCZImiZPyLGQBGRYDb3JnMRgwFgYKCZImiZPyLGQBGRYIRE9FR3JpZHMxIDAe
BgNVBAsTF0NlcnRpZmljYXRlIEF1dGhvcml0aWVzMRYwFAYDVQQDEw1ET0VHcmlkcyBDQSAx
MB4XDTA0MTEwOTE5MzIzOFoXDTA1MTEwOTE5MzIzOFowYzETMBEGCgmSJomT8ixkARkWA29y
ZzEYMBYGCgmSJomT8ixkARkWCGRvZWdyaWRzMQ8wDQYDVQQLEwZQZW9wbGUxITAfBgNVBAMT
GFJvYmVydCBELiBLZW5uZWR5IDYxMTAxNTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoC
ggEBAOO7G+xPZRPC4q0slJzT92R3t7KOo2hC9rNCDUIg2vfTH38EOxj0XKnVpeiqVpom059H
E9UiKdUj2AaVJS0jC7k475rMAh1bmWpPbKfzFKWOsS1Um8E6O9pQkMMAVPgSEy/bueUvsWTm
d98tWt2YekE7pdA6XGrx0Ardk+3njM2/dJfgJXcZllr8X7bpGLXt7ZCmEt9WHcMZUR4CULqo
+WjNYNKlfqD8RYAHW9v2OexVdp0iJSIUf7pY0U4N7PRn7dy01jOaICNloEayy3wT03DNqKl1
C7ctI23MwjJeFug0v55wrsvrVNmZvH76dVgsr8+9ESK6MegRn9X1L59prFMCAwEAAaNjMGEw
EQYJYIZIAYb4QgEBBAQDAgXgMA4GA1UdDwEB/wQEAwIE8DAfBgNVHSMEGDAWgBTKGR0Sjm6k
OF1C1DEOCNvZjRcNXTAbBgNVHREEFDASgRBrZW5uZWR5QGZuYWwuZ292MA0GCSqGSIb3DQEB
BQUAA4IBAQAIOOoxsfPbUXb4+vtpjnQxA773ephhAALSwNENyDGGEP7hQYa7amEKE6b+NR0T
eU0awwSCscHKW9hixQKy1pXrFeEMTOelPpTMHGhV6NRDlGJe4wytArA8r1h+l96zsXYWJ5xw
07y/UdFbM6SE9yB2xtictbZwHaCNaOFIEyB9AF4WOFk9XfvMP1pIqQC+S4vZL6q2GlsSQ34X
ucujZQ0nyn2kSxCCDha1pZiA+PEd/gkIU9IiYTRsq1VTJPm87nusFu4khF+MaxYyxLAUIf/0
WwfOrpv27tbeEKWzVTwMBdTg6B7El1JXxKkkNDdmT7B4PiTU0uQnHsF/03Wj3S4jMIID+DCC
AuCgAwIBAgIBCzANBgkqhkiG9w0BAQUFADB1MRMwEQYKCZImiZPyLGQBGRYDbmV0MRIwEAYK
CZImiZPyLGQBGRYCRVMxDjAMBgNVBAoTBUVTbmV0MSAwHgYDVQQLExdDZXJ0aWZpY2F0ZSBB
dXRob3JpdGllczEYMBYGA1UEAxMPRVNuZXQgUm9vdCBDQSAxMB4XDTAyMTIwNTA4MDAwMFoX
DTA4MDExMDA4MDAwMFowaTETMBEGCgmSJomT8ixkARkWA29yZzEYMBYGCgmSJomT8ixkARkW
CERPRUdyaWRzMSAwHgYDVQQLExdDZXJ0aWZpY2F0ZSBBdXRob3JpdGllczEWMBQGA1UEAxMN
RE9FR3JpZHMgQ0EgMTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBALT11iNho9sI
Pma1uJBvsprfLWoCbRlyooIVyJZx97wrBy7L22Me4iwt/1ki12QNbjHLyy5r2cmXHcqXCO26
ZMy062DfkpkKSdR3wozhUZNIV0tUb0Bs1rJ5/vpxpUIYzX6PIXQurTeRq4Y49Nw19l7VNlrd
7Vz2tzyWNXk5JZr+Z+wIALJLnMUha7TIgM3Il1/6fSHBo83nfCWWknfS1oP4kGNDuHaTjFFb
N5rOcs5v07O1lVED/WxXN76JzMWHbHBrhV0bLR4gg/DWl+9jDE7fqubRLXT2q9uw2Vqug9Fv
F6s8pqRAukp7TfhdzHuAE+pST8XGhFFaKfkRY3evP0sCAwEAAaOBnjCBmzARBglghkgBhvhC
AQEEBAMCAIcwDgYDVR0PAQH/BAQDAgHGMB0GA1UdDgQWBBTKGR0Sjm6kOF1C1DEOCNvZjRcN
XTAfBgNVHSMEGDAWgBS8XU1IL/g1lFmrXIlLPtGyOhQB6jAPBgNVHRMBAf8EBTADAQH/MCUG
A1UdEQQeMByBGkRPRUdyaWRzLUNBLTFAZG9lZ3JpZHMub3JnMA0GCSqGSIb3DQEBBQUAA4IB
AQAqO2KZPhCNM3B7KTVWI4MrsbYoPXbOApQ1CzOMob8oQjfiCnhRrXvCDqQVAQW0YC0jlGfd
S/hqMXEu4+uYONPVtgXZ8FJhNdSJgmq9QkaaucON49pdPcKjM05R62ppX2cSjGvdRbQVtKp4
/cpezbi/B7T+Qmvq28658e6lQBrHGHfoqCh2tmO2kUrKAkjVAmRqghON3TFySaX/v6OwD/TO
pb0R0N0gdTxp+7JAeDHuwAPRvK8ShjtE2pHqChypHVq1azuWPtV6W71U2e88eEcQhbgtKhrV
I8UpZVPHFAy9ugm3sZkI+/7q3yj0/IFZeQOdpxdA6cBHZqLl/y4jEUG7MYIDTjCCA0oCAQEw
bzBpMRMwEQYKCZImiZPyLGQBGRYDb3JnMRgwFgYKCZImiZPyLGQBGRYIRE9FR3JpZHMxIDAe
BgNVBAsTF0NlcnRpZmljYXRlIEF1dGhvcml0aWVzMRYwFAYDVQQDEw1ET0VHcmlkcyBDQSAx
AgIMODAJBgUrDgMCGgUAoIIBtDAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3
DQEJBTEPFw0wNTA5MDMxODUwMDJaMCMGCSqGSIb3DQEJBDEWBBRFJMbH0GRCSwl/+xRSsARm
6QbglTBSBgkqhkiG9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggq
hkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDB+BgkrBgEEAYI3EAQxcTBvMGkx
EzARBgoJkiaJk/IsZAEZFgNvcmcxGDAWBgoJkiaJk/IsZAEZFghET0VHcmlkczEgMB4GA1UE
CxMXQ2VydGlmaWNhdGUgQXV0aG9yaXRpZXMxFjAUBgNVBAMTDURPRUdyaWRzIENBIDECAgw4
MIGABgsqhkiG9w0BCRACCzFxoG8waTETMBEGCgmSJomT8ixkARkWA29yZzEYMBYGCgmSJomT
8ixkARkWCERPRUdyaWRzMSAwHgYDVQQLExdDZXJ0aWZpY2F0ZSBBdXRob3JpdGllczEWMBQG
A1UEAxMNRE9FR3JpZHMgQ0EgMQICDDgwDQYJKoZIhvcNAQEBBQAEggEAclKxIqtyPT9cYcua
ZhNtcm6ALpnXSM6QO8tbr5lcflyBOmPQpVSf5WNKnnqkK/NUHC0atXwples/iPW60x95XOUk
Fu9dDA9QohiuLV6rX6pd/n3V3CbBFQEUv/AFfSj2rQPYUpwzHvekjgN7FART6UZ+Fp5XQEYu
tZEN0zQUVqkpnP2AUKarlhB5IXAgUsbEOJgcm7n8/du7taGD0vojV/2ezWnKIc/iFmzUcUVJ
rfD9ItMAy3gi6VznB/iCAJ6Ek8PyMs9fP9L+k0FnUQImrzdjVyaz1sWHq1mLJbi8yixwccuv
HPGvKpaDc+U8i6iZGXUivxdSRTgTm+0YRco2TgAAAAAAAA==
--------------ms070808090907050103050607--