Scrigroup - Documente si articole

Username / Parola inexistente      

Home Documente Upload Resurse Alte 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



DOCUMENTE SIMILARE

Trimite pe Messenger
Tutorial - Simplifying your scripts by including files
Tutorial - Introduction to Object Oriented Programming
Apache, PHP
Use PostgreSQL and PHP on Windows
Tutorial - Creating and Using Views
Tutorial - Adding Data to your database
Tutorial - Referential Integrity
Introduction to PHP
Tutorial - Deleting the data in your database
Tutorial - Updating the data already in your database


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



DISTRIBUIE DOCUMENTUL

Comentarii


Vizualizari: 491
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 2021 . All rights reserved

Distribuie URL

Adauga cod HTML in site