SQL tables help!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

SQL tables help!!

Author

Topic

tonystarks
Starting Member

4 Posts

Posted - 2004-05-03 : 17:09:06

Hi, Im new to sql and currently working on designing a database but I've run into a few problems

I have a table called Designer where its properties are -

Designer_id - Primary key, 5 characters, uppercase

D_Name - Name of the designer. Character data up to characters
in length. Must not be null. Must be in upper case.

Supervisor_id- The id of the supervisor of the designer. May be null
but if present must match a value in Designer_id. A
designer cannot supervise onself.

From this I have written the code as follows but I am stuck on the Supervisor_id part -

CREATE TABLE Designer
(designer_id VARCHAR2(5)
CONSTRAINT pk_Designer PRIMARY KEY,
CONSTRAINT uc_designer_id CHECK (designer_id = UPPER(designer_id)),
d_name VARCHAR2(20) NOT NULL
CONSTRAINT uc_d_name CHECK (d_name = UPPER(d_name)),
Supervisor_id VARCHAR2(5)
<this parts missing>
);

I heard I had to use a Match <> operator or something but I cannot find anything about this anywhere??

Thats the first part to my question, I also have another query I am stuck on.

I have two other tables, first one is Application which has these properties -

ApplicationNo-Primary key of the relation. Up to five chars in length

AppName -Char field of up to 30 chars in length. Should be
stored in upper case. Must not be null and must not be
duplicated.

Appsize -Size of the application in Mb. Must be greater than zero.

CREATE TABLE Application
(applicationno VARCHAR2(5), appname VARCHAR2(30),
CONSTRAINT pk_Application PRIMARY KEY (applicationno,appname),
CONSTRAINT uc_appname CHECK (appname = UPPER(appname)),
appsize NUMBER(3)
CONSTRAINT ap_appsize CHECK (appsize > 0)
);

and this is the second table for Packagedapplication with properties-

Package_id -The id for the package this application is a part of.

Version -The version of the package this application is part of.

ApplicationNo-The id of one of the applications included in this
package. Must match a value in the Application table.

and this is the code I have used-

CREATE TABLE Packagedapplication
(package_id VARCHAR2(5)
CONSTRAINT pk_Packedapplication PRIMARY KEY,
version NUMBER(3,2)
applicationno VARCHAR2(5)
CONSTRAINT fk_applicationno references Application(applicationno)
);

I can create the Application table but when I try to create the Packagedapplication I get the following error -

(package_id VARCHAR2(5)
*
ERROR at line 2:
ORA-00922: missing or invalid option

Or if I don’t use a primary key for package_id, ie. I use this code -

CREATE TABLE Packagedapplication
(package_id VARCHAR2(5),
version NUMBER(3,2),
applicationno VARCHAR2(5)
CONSTRAINT fk_applicationno references Application(applicationno)
);

I get this error –

CONSTRAINT fk_applicationno references Application(applicationno)
*
ERROR at line 5:
ORA-02270: no matching unique or primary key for this column-list

I know this is a very long post and I would appreciate any help/comments anyone has to offer. Thanks everyone for reading through it

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 17:14:23

Is this for Oracle? VARCHAR2 is not a valid SQL Server data type. For Oracle questions, I would suggest posting your question at dbforums.com.

Tara

tonystarks
Starting Member

4 Posts

Posted - 2004-05-03 : 17:21:10

yeah this is for oracle, I will try at dbforums thanks Tara.

If anyone does know how to solve my probs plz feel free to reply as I will check also check this message.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources