1 / 14

CREATE TABLE ARTIST ( ArtistID int NOT NULL IDENTITY (1,1),

CREATE TABLE ARTIST ( ArtistID int NOT NULL IDENTITY (1,1), Name char(25) NOT NULL, TEXT ERROR Nationality char (30) NULL, Birthdate numeric (4,0) NULL, DeceasedDate numeric (4,0)NULL , CONSTRAINT ArtistPK PRIMARY KEY (ArtistID)

annamitchell
Download Presentation

CREATE TABLE ARTIST ( ArtistID int NOT NULL IDENTITY (1,1),

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CREATE TABLE ARTIST • ( • ArtistID int NOT NULL IDENTITY (1,1), • Name char(25) NOT NULL, TEXT ERROR • Nationality char (30) NULL, • Birthdate numeric (4,0) NULL, • DeceasedDate numeric (4,0)NULL , • CONSTRAINT ArtistPK PRIMARY KEY (ArtistID) • CONSTRAINT ArtistAK UNIQUE (Name) • );

  2. CREATE TABLE ARTIST ( ArtistID int NOT NULL IDENTITY (1,1), Name char(25) NOT NULL, Nationality char (30) NULL, Birthdate numeric (4,0) NULL, DeceasedDate numeric (4,0) NULL CONSTRAINT ArtistPK PRIMARY KEY (ArtistID) CONSTRAINT ArtistAK UNIQUE (Name) ); Also erroneous – see following slide

  3. Result of running CREATE TABLE ARTIST

  4. Altered CREATE statement • CREATE TABLE ARTIST • ( • ArtistID int NOT NULL IDENTITY (1,1) • CONSTRAINT ArtistPK PRIMARY KEY (ArtistID), • Name char(25) NOT NULL • CONSTRAINT ArtistAK UNIQUE (Name), • Nationality char (30) NULL, • Birthdate numeric (4,0) NULL, • DeceasedDate numeric (4,0)NULL • );

  5. CREATE TABLE example CREATE TABLE MYARTIST ( ArtistID int NOT NULL IDENTITY (1,1) CONSTRAINT ArtistPK PRIMARY KEY (ArtistID), Aname char(25) NOT NULL CONSTRAINT ArtistAK UNIQUE (Aname), Birthdate numeric(4,0) NULL, DeceasedDate numeric (4,0) NULL );

  6. Created to show effect of NO ACTION CREATE TABLE WORK ( WorkID int NOT NULL IDENTITY (500,1), Title char(25) NOT NULL, Copy char(8) NOT NULL, Description varchar (1000) NULL, ArtistID int NOT NULL CONSTRAINT WorkPK PRIMARY KEY(WorkID), CONSTRAINT WorkAK1 UNIQUE (Title, Copy), CONSTRAINT ArtistFK FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID) ON DELETE NO ACTION ON UPDATE NO ACTION We were NOT able to delete a row in ARTIST whose Artist ID was referenced by WORK

  7. Created to show effect of CASCADE CREATE TABLE SOMEWORK ( WorkID int NOT NULL IDENTITY (500,1), Title char(25) NOT NULL, Copy char(8) NOT NULL, Description varchar (1000) NULL, ArtistID int NOT NULL CONSTRAINT WorkPK3 PRIMARY KEY(WorkID), CONSTRAINT WorkAK3 UNIQUE (Title, Copy), CONSTRAINT ArtistFK3 FOREIGN KEY (ArtistID) REFERENCES MYARTIST (ArtistID) ON DELETE CASCADE ON UPDATE CASCADE ); We were able to delete a row in MYARTIST whose Artist ID was referenced by SOMEWORK and the referencing rows in SOMEWORK were also deleted.

  8. ALTER dropped column with no data in it • ALTER TABLE MYARTIST DROP COLUMN DeceasedDate;

  9. ALTER dropped column with data in it • ALTER TABLE ARTIST DROP COLUMN DeceasedDate;

More Related