Scrigroup - Documente si articole

     

HomeDocumenteUploadResurseAlte limbi doc
AccessAdobe photoshopAlgoritmiAutocadBaze de dateCC sharp
CalculatoareCorel drawDot netExcelFox proFrontpageHardware
HtmlInternetJavaLinuxMatlabMs dosPascal
PhpPower pointRetele calculatoareSqlTutorialsWebdesignWindows
WordXml

AspAutocadCDot netExcelFox proHtmlJava
LinuxMathcadPhotoshopPhpSqlVisual studioWindowsXml

Tutorial - Referential Integrity

php



+ Font mai mare | - Font mai mic



Tutorial - Referential Integrity  

Referential Integrity is an excellent way to stop discrepencies from entering your data.



This is a really basic introduction tutorial to get you started with the idea of referential integrity, and how you can use it to

protect your data.

We'll start off with some basic examples.

First, connect to your database:

tomcat@elrond ~$ psql -d tutorial

Now, we create the 'parent' table that the 'child' table refers to. In this example, we'll create two tables

(client and clientcontact) which lets you have multiple contacts for a client.

CREATE TABLE client (

clientid SERIAL NOT NULL PRIMARY KEY,

clientname VARCHAR(255)

Here's the 'child' table.

CREATE TABLE clientcontact (

contactid SERIAL NOT NULL PRIMARY KEY,

clientid int CONSTRAINT client_contact_check REFERENCES client(clientid),

name VARCHAR(255),

phone VARCHAR(255),

fax VARCHAR(255),

emailaddress VARCHAR(255)

After copying the table syntax into your PostgreSQL prompt, you'll receive some notices back:

NOTICE: CREATE TABLE will create implicit sequence 'clientcontact_contactid_seq' for SERIAL column 'clientcontact.contactid'

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'clientcontact_pkey' for table 'clientcontact'

NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

The first two should be familiar from previous examples.

The third one is the one we're most interested in. That means that the 'constraint' that we want is in place.

The SQL creating the table is slightly different. Note the line,

clientid int CONSTRAINT client_contact_check REFERENCES client(clientid)

So, as usual the field is called 'clientid', using an 'int' (integer) as the datatype. The next part means

create a constraint called client_contact_check, which refers to the clientid of the client table. The syntax for doing this:

<fieldname> <datatype> CONSTRAINT <constraint_name> REFERENCES

<table_name>(<field_to_reference>)

Now, if we add some data to our tables:

tutorial=> INSERT INTO client(clientid,clientname) VALUES (1,'Client 1');

tutorial=> INSERT INTO client(clientid,clientname) VALUES (2,'Client 2');

tutorial=> INSERT INTO clientcontact(clientid, name, phone, fax, emailaddress) VALUES (1, 'Chris', '12345', '67890',

'tomcat@designmagick.com');

tutorial=> INSERT INTO clientcontact(clientid, name, phone, fax, emailaddress) VALUES (1, 'Chris', '23456', '78901',

'chris@designmagick.com');

We'll check the data went into the database ok:

tutorial=> SELECT * FROM client;

clientid | clientname

1 | Client 1

2 | Client 2

(2 rows)

tutorial=> SELECT * FROM clientcontact;

contactid | clientid | name | phone | fax | emailaddress

1 | 1 | Chris | 12345 | 67890 | tomcat@designmagick.com

2 | 1 | Chris | 23456 | 78901 | chris@designmagick.com

(2 rows)

Now we'll try and delete the first client:

tutorial=> DELETE FROM client WHERE clientid='1';

ERROR: client_contact_check referential integrity violation - key in client still referenced from clientcontact

It won't let us! It would break the integrity of the data (since there is still a client with clientid of 1). So to actually

perform this action, we'd have to delete all of the contacts for the first client before we can remove the first client from the

table.

We can delete the second client (since nothing references it) :

tutorial=> DELETE FROM client WHERE clientid='2';

DELETE 1

If we try and add a contact for a client that doesn't exist (for example clientid of 3):

tutorial=> INSERT INTO clientcontact(clientid, name, phone, fax, emailaddress) VALUES (3, 'Chris', '12345', '67890',

'tomcat@designmagick.com');

We get an error:

ERROR: client_contact_check referential integrity violation - key referenced from clientcontact not found in client

So it doesn't let us put data into child tables without properly referencing the parent table (we can't add a contact for a

client that doesn't exist!).

These basic ideas can stop your data from becoming totally useless. There's not much use having a contact for a client that

doesn't exist!



Politica de confidentialitate | Termeni si conditii de utilizare



DISTRIBUIE DOCUMENTUL

Comentarii


Vizualizari: 776
Importanta: rank

Comenteaza documentul:

Te rugam sa te autentifici sau sa iti faci cont pentru a putea comenta

Creaza cont nou

Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved