SQL 'Create table with constraints'

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

General SQL Server Forums

New to SQL Server Programming

SQL 'Create table with constraints'

Author

Topic

cuckoo
Starting Member

5 Posts

Posted - 2009-01-29 : 18:09:17

Hello

I made a table like this :


CREATE TABLE ( Articles ) 
(
Article   int  NOT NULL PRIMARY KEY 
Accepted  ENUM(‘Yes’, ‘No’)  DEFAULT  ‘No’ ,
)

Let's say that there are inserted 500 articles. Now, our database should be able to accept only UP TO 200 articles that have 'Yes' in the field "Accepted". How can this be done? (I suppose that a single (CHECK ... etc) is not enough ).

Thank you

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-29 : 18:41:26

I do not think you can use this syntax in SQL 2000 or SQL 2005.

The ENUM datatype isn't supported but I will defer to the experts.

All I can tell you is that your code doesn't compile in my sql instance. Since you say you already made the table then I presume it is some other type of database platform?

r&r

cuckoo
Starting Member

5 Posts

Posted - 2009-01-29 : 18:58:49

ok in sql server 2005 , the code will be like this:


CREATE TABLE ( Articles ) 
(
Article   int  NOT NULL PRIMARY KEY 
Accepted varchar(3) CHECK(Accepted in ('Yes','No')) DEFAULT 'No'
)

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-29 : 19:13:46

Well I couldn't get that code to compile either so you may want to go back to the drawing board..

However if all you want to do is disallow future inserts of records if the database is already 'full' of accepted records then simple use an IF statement before you allow an insert...

DECLARE @Limit int
select @Limit=count(*) from Articles where Accepted='Yes'

IF @Limit <200
--Allow modifications to Articles (like updates, inserts)
ELSE
--Do not allow modifications to Articles (exit gracefully)

You could probably also do this with a CASE statement but I will leave that as an exercise for you to look up ; )

Is that what you are asking?

r&r

vrabasseda
Starting Member

4 Posts

Posted - 2009-01-29 : 19:25:12

Hello

Do you mean a conceptual design like this?

--------- 1 *--------------
|Article|-------|EnumAccepted|
--------- --------------

With this logical design
(name type constraint default check)
(primary keys underlined)

---------------------------
| Article |
|--------------------------
|article int PK |
|------- |
|accepted int FK,NN 0 |
---------------------------

---------------------------
| EnumAccepted |
|--------------------------
|idAccepted int PK |
|------- |
|accepted nvarchar(3) NN |
---------------------------

What in sql sever could be coded as

create table EnumAccepted(
idAccepted int constraint PK_EnumAccpeted_idAccepted primary key identity,
accepted nvarchar(3) constraint NN_EnumAcepted_accepted not null
)
go

create table Article(
article int constraint PK_Article_article primary key identity,
accepted int default 1 constraint NN_Article_accepted not null
constraint FK_Article_accepted references EnumAccepted(idAccepted)
)
go

insert into EnumAccepted(accepted) values('No');
insert into EnumAccepted(accepted) values('Yes');

Then to ensure there wont be more than 200 articles with 'yes' in the accepted field you have two choices: A trigger that returns an error if there's an atempt to bypass this number of 'yes' or a procedure to make inserts on the Article table and does de job of counting 'yes' and return an error if its equals to 200

I could show you how to write at least one of them

create procedure insertArticle(@accepted int)
as
declare @cn int;
begin
set @cn=(select count(*) from Article a inner join EnumAccpeted b on a.accepted=b.idaccepted
where b.accepted='yes');
if @cn<200
insert into Article(accepted) values (@accepted)
else
print 'ERROR: Too many articles.'
end

But this aproach is very simple. You need that users that execute this procedure to insert articles could not insert them with an insert query. And this means schemas, users and permissions task.

Read my issue Schemas and Permissions where I ask some questions about this topic.

Hope this helps.

<i>V Rabasseda </i>

cuckoo
Starting Member

5 Posts

Posted - 2009-01-29 : 20:13:24

Thank you for your answers, I am now starting to understand how things work

This check should be done every time the "secretary" tries to insert a new article and it's status (Accepted or not). So our database, on every insert should check if the total of the 'Yes' (accepted - already registered articles) is 200
1) If it is 200 and the article to be registered is a 'Yes' too, it should give an error message.
2) If it is 200 , but the article is a 'No' , the article should be registered with a 'No, without a problem.
3) If it is less than 200, the article is registered without a problem , no matter if it is accepted ('Yes'), or not ('No').

So the error message , pops up only if the secretary tries to insert an accepted article ('Yes') AND the list has already 200 accepted articles.

The fact that this requires a check on every insert, requires a trigger? Or it can be done with a procedure too? How does a trigger like this look like?

Thank you for your time, as u can tell I am a beginner, trying to learn from the code I see.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 02:13:03

if you want to do check for each insert, a trigger will be required. However you could also do this logic in insert sp which inserts the records if you can make sure inserts will always be done using sp rather than by adhoc queries.

cuckoo
Starting Member

5 Posts

Posted - 2009-01-30 : 17:02:03

Can triggers be made in mysql or only sql server/oracle? If yes, do they have the same form?

vrabasseda
Starting Member

4 Posts

Posted - 2009-01-30 : 19:04:15

Hello

Sql is a standard for structured query language and all relational databases should follow its specifications.

On the other hand some servers have extended sql objects and sentences, but if you write sql code in standard sql you'll have no problem at all.

Of course you would say native sql should be more effective or optimized. Not true. Each server has its own syntactic and sometimes semantic optimizer, then no warry about sql particularities, just try to learn standard sql that includes queries, triggers and procedures.

This is a Sql Server page and I think not the best place to discuss other servers architecture. But try to pay a visit to that page:

http://www.w3schools.com/sql/default.asp

I hope this answer your question.

<i>V Rabasseda </i>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:03:03

quote:
Originally posted by cuckoo

Can triggers be made in mysql or only sql server/oracle? If yes, do they have the same form?

yup...you can have triggers in mysql also. why are you using mysql?

cuckoo
Starting Member

5 Posts

Posted - 2009-02-01 : 09:25:45

Thank you all for your answers. Because this is for a school project and what matters more is the code
itself, we won't show the database. I write it in mysql because sql server was much larger to download and install. Mysql seems easier.

ok after creating this table in mysql :


CREATE TABLE Article
(
Art_ID  int  UNSIGNED NOT NULL  PRIMARY KEY  AUTO_INCREMENT ,
Accepted  ENUM('Yes','No') ,
Test varchar(20),
CHECK (Art_ID>0)
);

I am trying to make a simple trigger (to begin with), to see how it works. I write this as a query:


CREATE TRIGGER tr1 BEFORE INSERT ON Article
FOR EACH ROW
BEGIN
IF NEW.Accepted='Yes' THEN
SET NEW.Test='Yes inserted';
ELSEIF NEW.Accepted='No' THEN
SET NEW.Test='No inserted';
END IF;
END;

with no success. I am using mysql 5.1 . Is it totally wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 12:24:22

this is MS SQL Server forum. you need to post in some MySQL forumes if you need mysql syntax. try www.dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources